Trying to create a % calc across multiple dimensions by month

[timmarsh]

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:

1. Plot the sum of a counter (eDS_Ctr) by MonthYear, for a second dimension (called eDS_Status). This works fine. See below image 1.
2. Plot the proportion for each eDS_Status in that month. At the moment I am using the below sum (bolded), which I know is not right as it is summing the total eDS_Ctr, not just for that month, which is giving me very low %s which I know are not right. My line graph has MonthYear as the primary dimension and eDS_Status as secondary. See image 2.

=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!

[Anil_Babu_Samineni]

Perhaps this

=sum(eDS_Ctr)/Sum(aggr (sum(eDS_Ctr), MonthYear))

[timmarsh]

Hi Anil, thanks for the response - I tried that.

I still get a whole bunch of no results in the Pivot table.

Tim

[timmarsh]

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.

[timmarsh · Accepted solution]

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.

[Brett_Bleess]

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