topic Re: Set Analysis inside Aggr() function in QlikView

Set Analysis inside Aggr() function

saifuddin — Sat, 16 Nov 2024 01:50:28 GMT

Hi All,

I need help with a query. I am trying to show avg of the mark for current and previous Trimester. I am using set analysis inside an aggr() function.

=SUM(AGGR(SUM({<Trimester={'$(vTrimester)'}>} DISTINCT( POINTS/6*Score)), ID, Scode, START_DT))
/
SUM(AGGR(SUM({<Trimester={'$(vTrimester)'}>}DISTINCT(POINTS/6)), ID, Scode, START_DT))

For the current selection, it works fine.

the issue is with the previous Trimester. it doesn't show any data.

=SUM(AGGR(SUM({<Trimester={'$(vTrimester01)'}>} DISTINCT( POINTS/6*Score)), ID, Scode, START_DT))
/
SUM(AGGR(SUM({<Trimester={'$(vTrimester01)'}>}DISTINCT(POINTS/6)), ID, Scode, START_DT))

another requirement I have is to show avg of last five Trimester.

I have attached the excel file and the QVW for more info

I appreciate your help.

Regards,

Saif

Re: Set Analysis inside Aggr() function

CELAMBARASAN — Wed, 27 Nov 2019 05:25:20 GMT

When using AGGR & Set Analysis, you should apply set analysis inside & outside Aggr. Otherwise outside Sum will work for Current selections, Sum inside the Aggr works for the Set analysis filtered data so the result will be Intersection of Selection & set analysis.

PFB the expression

=SUM({<Trimester={'$(vTrimester01)'}>} AGGR(SUM({<Trimester={'$(vTrimester01)'}>} DISTINCT( POINTS/6*Score)), ID, Scode, START_DT))
/
SUM({<Trimester={'$(vTrimester01)'}>} AGGR(SUM({<Trimester={'$(vTrimester01)'}>}DISTINCT(POINTS/6)), ID, Scode, START_DT))

I will suggest you to correct Current Trimester expression as well

=SUM({<Trimester={'$(vTrimester)'}>}AGGR(SUM({<Trimester={'$(vTrimester)'}>} DISTINCT( POINTS/6*Score)), ID, Scode, START_DT))
/
SUM({<Trimester={'$(vTrimester)'}>}AGGR(SUM({<Trimester={'$(vTrimester)'}>}DISTINCT(POINTS/6)), ID, Scode, START_DT))

Re: Set Analysis inside Aggr() function

saifuddin — Wed, 04 Dec 2019 23:01:36 GMT

Thanks for your help