Ageing buckets with calculated dimension and date variable

Mon, 27 Mar 2017 18:12:39 GMT

Hi,

I have a calculated dimension that I am using to form the columns in a pivot table (to create ageing buckets):

IF((Date(vSnapshotDate) - Date([Z_Calc due date])) <= 0, '1. Current',

IF((Date(vSnapshotDate) - Date([Z_Calc due date])) <= 30, '2. <=30',

IF((Date(vSnapshotDate) - Date([Z_Calc due date])) <= 60, '3. <=60',

IF((Date(vSnapshotDate) - Date([Z_Calc due date])) <= 90, '4. <=90',

IF((Date(vSnapshotDate) - Date([Z_Calc due date])) <= 180, '5. <=180',

IF((Date(vSnapshotDate) - Date([Z_Calc due date])) <= 360, '6. <=360',

IF((Date(vSnapshotDate) - Date([Z_Calc due date])) > 360, '7. >360',

'0.Unknown')))))))

vSnapshotDate is a variable that is set via the user selecting a date from a drop-down (option variable) list. The variable is definitely getting updated correctly, as it is populating and updating in a straight table. The total of the columns in the pivot table are also correctly updating based on the selection of vSnapshotDate.

However, the split of that total between the buckets is not correct.

If I add the above expression to a straight table as a dimension, I can see that it does not calculate correctly. However, if I add it as a measure to the straight table then it is calculating correctly. I think when used as a dimension, it is not recalculating based on the current value of vSnapshot date, but is instead 'remembering' the result of the first vSnapshotDate selection and result of that calculation.

So clearly there is an issue with getting the expression to evaluate correctly on change of vSnapshotDate when it is added to an object as a dimension.

Can anybody advise on how to create this bucketing?

The data set loaded is at the most granular transactional level, and the calculation of Date(vSnapshotDate) - Date([Z_Calc due date]) is applicable to each row of the data set (i.e. no aggregation needed).

I cannot use the class function (due to irregular bucket sizes required by the client), and I cannot create buckets in the load script due to the user dynamically setting the snapshot date variable.

Re: Ageing buckets with calculated dimension and date variable

sunny_talwar — Mon, 27 Mar 2017 18:15:53 GMT

Would you be able to share a sample to show the issue?

topic Re: Ageing buckets with calculated dimension and date variable in App Development

Ageing buckets with calculated dimension and date variable

Re: Ageing buckets with calculated dimension and date variable