Question regarding active forms

[ChrisF79]

I just made a spreadsheet using active forms that pulls our customers that have outstanding balances for AP. The problem is, the list is about 6000 customers long. I'd like to make the list filtered in some way for accounts that are past due. I have an attribute on the customer dimension that tells me the date they last paid so can I somehow make the active form look at that and only show me customers that haven't paid in over 30 days? I just need to come up with a way to pair that list down.

Thanks!

[tomok]

Create another attribute called "Past Due" and calculate the value in this attribute by looking at the current date, the date last paid, and putting a 1 or "Y" in this attribute if the 1) the difference is greater than 30 and 2) they have an outstanding balance in AP (I assume you have this in another cube somewhere). You then create a dynamic subset filtering on the Past Due attribute and use this subset in the TM1RPTROW formula for the Customer column.

[lotsaram]

You could create a dynamic subset and reference this in the subset argument of the TM1RptRow formula as per Tomok's suggestion. However it would be much better to have the same MDX logic as a string or parsed formula in the worksheet and use the MDX Expression argument of the TM1RptRow formula instead. This would avoid potential locking and performance issues with dynamic subsets and also allow for much greater flexibility and functionality with your filter. For example with a numeric "days past due" attribute you could easily have an on the fly configurable "> X days" type filter and you could even cross reference to the accounts receivable balance for a combined "> X days AND > Y dollars" filter.

Then you would have a report that is really adding value.

[ChrisF79]

Strange, but I'm trying to calculate the number of days between two dates and getting an error in the process. The code is:

Code: Select all

IF ( (vlastsale @<> 'NO LAST SALE') % (vlastpay @<> 'NO LAST PAY') );
     LastSaleDays = DAYS360( NOW() - DATEVALUE(vlastsale));
     LastPayDays = DAYS360(NOW() - DATEVALUE(vlastsale));
ENDIF;

Normally, vlastsale or vlastpay will have a date in the format of MM/DD/YYYY and set as string.

When I try to save this TI process, I get an error stating:
Variable Days360 is undefined. I tried looking this up in help and I see it there so I'm not sure what's going on. If I can't use that function, is there another way I could calculate the number of days between two dates?

[tomok]

I see you are using two "new" TM1 functions, DAY360 and DATEVALUE. Actually, these functions don't exist in TM1. Those are Excel functions, you can't use them in rules or in a TI process in TM1. You can only use functions that are available in the TM1 Rules language or TI-only functions. You can find all the functions available to you in the IBM Cognos TM1 Reference Guide from the Help menu. I think this might work:

LastSaleDays = NOW - DAYNO(vlastsale);
LastPayDays = NOW - DATNO(vlastpay);

However, vlastsale and vlastpay need to be in YY-MM-DD or YYYY-MM-DD format instead of the MM/DD/YY.