Skip to main content

I am trying to identify the most recent date in the transaction list at the vendor, IS0, and Function level. I am using a MAXOF (date) formula and aggregating by desired dimensions. Any idea why this formula isn’t working?

 

@ljanowski I had the same problem before. What I did is 

1 create a metrics like: 
Max_Date = transaction_list.Date_ColumnCby max: transaction_list.dimension1,transaction_list.dimension2,...]

2 create another column in the transaction list 

Max_Date_Column = Max_DateMby constant: dimension1->transaction_list.dimension1]eby constant: dimension2->transaction_list.dimension2]

 

Hope it helps.


Hi ​@ljanowski ,

The MAXOF function would result in the max for the full column which results if tested in the playground to the max date with no dimensions:
 

The BY modifier used afterwards would only allocate the same value to all rows.
To get the result based based on scope you would need to aggregate the date using a BY MAX as explained​@Min Li Bright Point  and allocate it to your transaction list with an additional BY to remove the dimensions and use the mapping defined in the properties (a simple BY would be sufficient ).
Your formula would be:

TL.'Created on'
oBY MAX: TL.Vendor,TL.Function,TL.ISO]
SBY CONSTANT: TL.Vendor,TL.Function,TL.ISO]


Where:

  • BY MAX calculates the max date per group.

  • BY CONSTANT maps it back to each row in the transaction list.

Hope this provides more clarifications,
Issam


Reply