Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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))
Thanks for your help