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: 
KD-Qlik
Contributor II
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
KD-Qlik
Contributor II
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)

KD-Qlik
Contributor II
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)