# App Development

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor II

## Cross table/Distinct Sum

I have just unpivoted a table/cross tabled and unfortunately that has meant my sum formula is now summing the duplicate values from the unpivot, Is there a better way to handle this other than editing my master item to include a distinct check?

Saying that I am also slightly stuck trying to fit distinct in so any help would be appreciated!

What I currently have (worked before the unpivot):

Sum(if(IsNull([source name]),[Number Arrived]=0,[Number Arrived]))

I now need to include "only do the above calculation for records with unique [ID]"

edit: I have my Master Item for DistinctNos and MasterItem for NumbersIn and thought I would be able to combine them in a separate item but no luck 😞

DistinctNos = sum(aggr(sum(DISTINCT [Number Arrived]), [ID]))

NumbersIn = Sum(if(IsNull([source name]),[Number Arrived]=0,[Number Arrived]))

I thought I would be able to say

Any help would be appreicated!

Thanks

2 Replies
Contributor II
Author

https://community.qlik.com/t5/QlikView-App-Dev/Sum-of-Sales-where-ID-is-DISTINCT/m-p/868082

^ I have found the above which returns the correct number when filtered (So now I just need to try work it into my formulas! (Will edit the answer when I work it out)

Contributor II
Author

Not how I wanted to solve it but it's solved anyway:

DistinctNos = sum(aggr(sum(DISTINCT [Number Arrived]), [ID]))

AND Unselecting the null value for dimension on the chart options.

(NumbersIn is now no longer needed)

Tags
Community Browser