topic Re: Neseted aggregation in QlikView

Neseted aggregation

ethanch — Sat, 16 Nov 2024 19:12:17 GMT

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.

Re: Neseted aggregation

petter — Thu, 30 Jan 2020 22:01:52 GMT

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 )

Re: Neseted aggregation

ethanch — Thu, 30 Jan 2020 23:20:17 GMT

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

Re: Neseted aggregation

marcus_sommer — Fri, 31 Jan 2020 12:19:57 GMT

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

Re: Neseted aggregation

ethanch — Fri, 31 Jan 2020 21:01:39 GMT

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

Re: Neseted aggregation

marcus_sommer — Mon, 03 Feb 2020 08:16:50 GMT

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

Re: Neseted aggregation

ethanch — Mon, 03 Feb 2020 19:13:03 GMT

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.

Re: Neseted aggregation

cengizeralp — Mon, 03 Feb 2020 20:23:22 GMT

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)

Re: Neseted aggregation

ethanch — Mon, 03 Feb 2020 20:33:19 GMT

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.