# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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.

Tags (5)
Labels (5)

• ### sum

1 Solution

Accepted Solutions MVP

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

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

Try this

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

## 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))``
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. MVP

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

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

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

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

Highlighted
Contributor II

Marvelous!

Thanks Sunny!