Percentage distribution of sales in different Cities

[kamal_sanguri]

Hi All,

Please check the attached QVW. I am stuck in a situation where I need to calculate the % distribution.

I checked many similar threads but they all were unanswered..

I want to calculate the last column in the object, For some reason, I get value in one cell but not in remaining.

Any help would be greatly appreciated.

Thanks,

-Kamal

[sunny_talwar · Accepted solution]

Try this:

=Sum(Sales)/Aggr(NODISTINCT Sum(Sales),Country)

[sunny_talwar]

Alternatively, this should also work:

=Sum(Sales)/Sum(TOTAL <Country> Sales)

[kamal_sanguri]

Terrific thanks... Sunny!!

This thread is definitely going to help other as well..

Could you please explain both of them, What happened when did you add Nodistinct which I missed.. and second one is totally new for me...

Thanks again..

-Kamal

[sunny_talwar]

1st expression: The issue is that Aggregate by default shows DISTINCT value. So for each country it was showing the Sum at one of the cities that were available. To get the denominator to show value for all cities, we need to add NODISTINCT.

2nd Expression: Try adding Sum(TOTAL <Country> Sales) as as expression and it will show you the Total by Country. If you want the complete TOTAL you would use Sum(TOTAL Sales). So to get total by one of your dimension you can specify it as a field within <> with a total qualifier. (QlikView ‒ Nested aggregations)

Out of the two, I would recommend using the second expression because I have been told and have noticed that Aggr takes more resources.

HTH

Best,

Sunny

[kamal_sanguri]

Thanks for this beautiful explanation...

Regards,

-Kamal