Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
ethanch
Contributor II
Contributor II

Neseted aggregation

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. 

Labels (3)
1 Solution

Accepted Solutions
cengizeralp
Contributor III
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)

 

View solution in original post

8 Replies
petter
Partner - Champion III
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 )

 

ethanch
Contributor II
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).

 

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
Contributor II
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

 

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
Contributor II
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. 

cengizeralp
Contributor III
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)

 

ethanch
Contributor II
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.