Neseted aggregation

[ethanch]

Hi all, 

I am having trouble working with aggregation, please help me out here.

What I want to achieve: sum(if([Reporting Year]=(CurrentYear) and Month=max(Month) ,Numerator))

I want the sum of Numerator in the newest month. And =CurrentYear which is a custom field to set the year.

I tried sum(if([Reporting Year]=(CurrentYear) and Month=Aggr(max(Month),[Reporting Year],Measure) ,Numerator)), but it is giving me unstable performance. 

I am very new with Aggr(), I am not sure if I am doing it right. 

Any input and advise is appreciated. 

[petter]

I think you could do this:

Sum( {<[Reporting Year]={$(=Year(Today()))},Month={'$(=Max(Month)')}>} Numerator )

You could also set the CurrentYear in a variable and use that instead of Today():

vCurrentYear = Max(Year(TransDate))  

vMaxMonth = Max(Month)

Sum( {<[Reporting Year]={$(vCurrentYear)},Month={'$(vMaxMonth)'}>} Numerator )

[ethanch]

thanks for your response, but I am got 0 from 

Sum( {$<[Reporting Year]={'$(vCurrentYear)'},Month={'$(vMaxMonth)'}>} Numerator )

The Correct result should be 7. 

I used 

Sum( {$<[Reporting Year]={'=$(vCurrentYear)'},Month={'=$(vMaxMonth)'}>} Numerator )

but that is equal sum({$}Numerator).

[marcus_sommer]

If your field Month is created from a date-field and has values like 'Jan', 'Feb', ... then would the call of:

Max(Month)

return 1, 2, ...

and both won't match. In this case I would recommend to add an appropriate field - num(month(DATE)) as MonthNum - within the master-calendar and then using this for the comparing.

- Marcus

[ethanch]

I tested both variable in a textbox and they show correct value,

=$(vCurrentYear)        Show 2019

=$(vMaxMonth)           Show 11

=Sum( {$<[Reporting Year]={'2019'},Month={'11'}>} Numerator )   Show 958, the correct sum

=Sum( {$<[Reporting Year]={'=$(vCurrentYear)'},Month={'=$(vMaxMonth)'}>} Numerator )    Show 9796

=Sum( {$} Numerator )   Show 9796

=Sum( {$<[Reporting Year]={'=$(vCurrentYear)'}>} Numerator )   Also Show 9796, it is not just the problem with Month

[marcus_sommer]

Use the following of your trials within a table-chart without using a label for the expression:

=Sum( {$<[Reporting Year]={'=$(vCurrentYear)'},Month={'=$(vMaxMonth)'}>} Numerator )

If you now hover on the label you could see how Qlik interprets your expression and the variables inside. I could imagine that the equal-sign within the set analysis condition caused your issue - therefore remove it and try it again, means:

=Sum( {$<[Reporting Year]={'=$(vCurrentYear)'},Month={'$(vMaxMonth)'}>} Numerator )

- Marcus

[ethanch]

The label in the table-chart for the expression:

=Sum( {$<[Reporting Year]={'=max(CurrentYear)'},Month={'=Max(Month)'}>} Numerator )

and it is showing the same incorrect answer. 

After removing the equal sign, it result in 0. 

[cengizeralp · Accepted solution]

Hi Ethanch,

If you define below variables from the front end, please check if there is an equal sign in front of them.

If there isn't, could you try again Peter's and Marcus' offers after adding  equal sign in front of the variables expressions.

vCurrentYear     = Max(Year(TransDate))  

vMaxMonth        = Max(Month)

[ethanch]

Thank you for the answer, that is what I am missing. 

My final expression with correct result:

=Sum( {$<[Reporting Year]={'$(vCurrentYear)'},Month={'$(vMaxMonth)'}>} Numerator )

Thanks everyone for the help.