Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ZoeM
Specialist
Specialist

The Sum of a dimension field based on unique (distinct) dimension

Hello Community.

I am trying to calculate the sum of one of my dimension fields and show it in a text box as a KPI. Simple enough.

The issue is I need it to be distinct based on another field. Here is my Situation:

Sum of # of days Late for Product A & B. Lets say this should be 50 days late. Product A is 15 and Product B is 35.

Product A has multiple rows because product A has differing ingredients e.g. Sugar, salt, milk, flour etc and each of those rows is listed as 15 days late because they roll into Product A which is 15 days late.

However, in Qlik the result I am getting is 95 (15 for Product A - Salt, 15 for Product A - milk,   15 for Product A - Sugar, 15 for Product A - flour and 35 for Product B). 

This is not the true case, it should only be 50. Is there a way to create a distinction on how the calculation is to be made? 

I hope I have illustrated this well and any help rendered will be truly appreciated. 

Thanks in advance.

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Try this

=Sum({$<[#of Days Late]={'>14'},[Date Type]={'VPP Dates'},[Reference_Only]={'No'}>} Aggr([#of Days Late], Gateway))

View solution in original post

8 Replies
sunny_talwar

Try this

Sum(Aggr(Days, Product))
ZoeM
Specialist
Specialist
Author

Thanks for the response Sunny. But that didnt work. 

Here is the ideal Set analysis Expression if I didnt have multiple line items:

=Sum({$<[#of Days Late]={'>14'},[Date Type]={'VPP Dates'},[Reference_Only]={'No'}>}[#of Days Late])

where

[#of days Post Late] is a dimension and are selecting days late greater than 14 days

The dimension with the multiple line items is called Gateway. So each Gateway will have multiple items with the same #of Days Post.

Hope this will be 

 

sunny_talwar

Try this

=Sum({$<[#of Days Late]={'>14'},[Date Type]={'VPP Dates'},[Reference_Only]={'No'}>} Aggr([#of Days Late], Gateway))
ZoeM
Specialist
Specialist
Author

Thanks for the effort but yea that didn't work either. Missing something. 

 

 

sunny_talwar

Can you elaborate on what do you mean when you say it is missing something? Do you get nulls or unexpected output?

ZoeM
Specialist
Specialist
Author

Oh Sunny! You are going to be mad at me. 

Your expression worked lol, but I had to add another dimension to the aggregation portion of the Set Analysis at the end:

=Sum({$<[#of Days Late={'>14'},[Date Type]={'VPP Dates'},[Reference_Only]={'No'}>} Aggr([#of Days Late],Gateways,[MY Program] ))

Thansk for your help Sunny!

I will play with this more and see if I will have any issues!

 

sunny_talwar

No reason to be mad my friend... Also, just to make it better, you should add the same set analysis used in outer Sum, inside your field within Aggr

=Sum({$<[#of Days Late={'>14'},[Date Type]={'VPP Dates'},[Reference_Only]={'No'}>} Aggr(Only({$<[#of Days Late={'>14'},[Date Type]={'VPP Dates'},[Reference_Only]={'No'}>} [#of Days Late]), Gateways, [MY Program]))

 

ZoeM
Specialist
Specialist
Author

Marvelous!

Thanks Sunny!