Max date in expression works, but not in Set Analysis

[dena_reavis]

Hello experts, 

I am trying to get the sum of inventory as of the last day of activity in a month - which might not be the month ending date. For example, July 29 could be the last day of activity in July, and Aug 19 is the latest date of activity in August.

So we started with a set analysis using the Max Inventory Date and it didn't return a sum for all months in the pivot table. (Month is a dimension in the pivot table).

So to troubleshoot, I use this as an expression by itself:

     Max(Inventory.Date) which does work, and so does Max(TOTAL <Month, Year> Inventory.Date)

But this doesn't work: 

     Sum({$<Inventory.Date={"$(=Max(Inventory.Date))"}>}Inventory.Tons)

So in case the Inventory.Date is interpreted as a number instead of a date in the set, I added the 'M/D/YYYY', still doesn't work. 

     Sum({$<Inventory.Date={"$(=Date(Max(Inventory.Date),'M/D/YYYY')"}>}Inventory.Tons)

I did confirm there is data on the latest days of July and August to sum. 

Any suggestions? 

[H_Julian]

You can not use Max/Min expression within aggregation.

Instead flag those entries within the load script e.g. with an 'X' as "Flag_Max"(or Flag_Min)

and write Flag_Max={'X'}

within the aggregation

Greetings

[dena_reavis]

I wanted to try this in the set analysis because we could have a lot of this type of thing.  What do you mean 'can not use Max/Min expression within aggregation'?...  I found these references to similar issues where the solution involved using Max date in the set. 

Set Analysis max date 

Set Analysis and max date 

Set Analysis use of max date 

I am pretty sure this can be done, but I must have the syntax wrong somewhere.

[marcus_sommer · Accepted solution]

In general you could use a construct like: {"$(=Max(Inventory.Date))"} and with an appropriate formatting it would be suitable for a string-comparing. But the used approach with the $-sign expansion creates an adhoc-variable which is calculated globally without considering any dimensionality of the object and before the chart creation/calculation starts and the result is then applied to all rows.

Therefore I assume that's rather unlikely that you could apply this logic. Even if there are multiple similar requirements it may in the end be easier and more performant to create several (multi-value) flags within the datamodel und to use them within the UI for your views.

Beside this you may try something like this:

{"=Inventory.Date=Max(Inventory.Date)"}

but it's no "real" set analysis anymore because it forces a comparing on the record-level which means it's similar to an if-loop like:

if(Inventory.Date=Max(Inventory.Date), sum(Inventory.Tons))

- Marcus