1 Reply Latest reply: Mar 27, 2017 2:15 PM by Sunny Talwar RSS

    Ageing buckets with calculated dimension and date variable

    Mark Wallington

      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.