# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for
Did you mean:
Creator III

## 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.

Labels (3)

• ### Set Analysis

1 Solution

Accepted Solutions
MVP

Try this

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

Try this

``Sum(Aggr(Days, Product))``
Creator III
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

MVP

Try this

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

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

MVP

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

Creator III
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] ))

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

MVP

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]))``

Creator III
Author

Marvelous!

Thanks Sunny!

Tags
Community Browser