Qlik Community

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

ZoeM
Contributor II

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.

1 Solution

Accepted Solutions

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

Try this

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

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

Try this

Sum(Aggr(Days, Product))
Highlighted
ZoeM
Contributor II

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

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 

 

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

Try this

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

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

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

 

 

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

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

ZoeM
Contributor II

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

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!

 

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

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
Contributor II

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

Marvelous!

Thanks Sunny!