Set Analysis inside Aggr() function

[saifuddin]

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 

[CELAMBARASAN · Accepted solution]

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

[saifuddin]

Thanks for your help