Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all, I did a search first before posting this so as to not waste anyone's time.
(I have a QVW and QVDs if needed)
Ok, so I have some data where I want to:
=sum(eDS_Ctr)/sum (ALL eDS_Ctr)
Image 1 - Volume (this is fine)
Image 2 - Here is proportion/%, not ok.
I can validate what number I think should appear for each month, for each eDS_Status by looking at this Pivot table. Below, Image 3
Image 3
I thought the right expression might have been per the below but that only gives me the right calculation for one of the eDS_Status values as shown in the last column of the Pivot.
=sum(eDS_Ctr)/(aggr (sum(eDS_Ctr), MonthYear))
I know this will be a simple solution - sorry, have not yet turned my mind to aggr as much as I should!
Many thanks for any help!
Quick update.
This expression works.
=sum(eDS_Ctr)/sum(TOTAL<MonthYear>(eDS_Ctr))
I'd tried ALL but not TOTAL. Silly me.
Also added exclusion for selection in status.
=sum(eDS_Ctr)/sum({$<eDS_Status=>}TOTAL<MonthYear>(eDS_Ctr))
Perfect. Thanks Anil!
PS: it would be awesome to understand why the aggr threw a null for nearly all the EDS_Status but correct value for one.
Perhaps this
=sum(eDS_Ctr)/Sum(aggr (sum(eDS_Ctr), MonthYear))
Hi Anil, thanks for the response - I tried that.
I still get a whole bunch of no results in the Pivot table.
Tim
Hi again Anil
So it looks like the % calc (aggr) gives a null for all Dimension values except one.
Which we can see is false, as there is a value for the others.
Quick update.
This expression works.
=sum(eDS_Ctr)/sum(TOTAL<MonthYear>(eDS_Ctr))
I'd tried ALL but not TOTAL. Silly me.
Also added exclusion for selection in status.
=sum(eDS_Ctr)/sum({$<eDS_Status=>}TOTAL<MonthYear>(eDS_Ctr))
Perfect. Thanks Anil!
PS: it would be awesome to understand why the aggr threw a null for nearly all the EDS_Status but correct value for one.
Hey Tim, best I have on the AGGR piece is the following Design Blog post, hopefully that may explain something for you:
https://community.qlik.com/t5/Qlik-Design-Blog/Pitfalls-of-the-Aggr-function/ba-p/1463275
Regards,
Brett