Showing the value for a specific month, when dimension is also month

[#1]

Hi,

I'm trying to use set analysis to get a value for a month according to certain filtering. But I can't get this to work the way I need it to.

Here's a simplified version of my calendar:

Months:
LOAD * INLINE [Month, OfficialReportingPeriod
1, 1
2, 1
3, 1
4, 1
5, 1
6, 1
7, 1
8, 1
9, 0
10, 0
12, 0];

And here's a simplified version of my data:

Values:
LOAD * INLINE [Month, Value
1, 400
2, 450
3, 430
4, 420
5, 510
6, 580
7, 560
8, 490
9, 390
10, 470,  
11, 510
12, 520];

I'm showing values by month ( dimension : month, expr1 : sum(Value) ), but I want a second expr which would always show only the value of latest month where OfficialReportingPeriod = 1. Currently I'm trying something like this:

=sum(TOTAL
  {$<Month = {"$(=max(TOTAL {$< OfficialTimePeriod = {'1'} >} Month))"}>}
  Value)

But this returns the value of month 12 (520), not month 8 (490).

[sunny_talwar · Accepted solution]

Try this:

=FirstSortedValue(TOTAL {$<OfficialReportingPeriod = {1}>} Aggr(Sum({$<OfficialReportingPeriod = {1}>} Value), Month), -Month)