cancel
Showing results for
Did you mean:
Contributor II

## Neseted aggregation

Hi all,

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.

Labels (3)

• ### If stament

1 Solution

Accepted Solutions
Contributor III

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)

8 Replies
Partner - Champion III

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 )

Contributor II
Author

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).

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

Contributor II
Author

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

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

Contributor II
Author

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.

Contributor III

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)

Contributor II
Author

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.

Community Browser