topic Re: Cross table/Distinct Sum in App Development

Cross table/Distinct Sum

KD-Qlik — Fri, 06 Aug 2021 13:59:49 GMT

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

Re: Cross table/Distinct Sum

KD-Qlik — Fri, 06 Aug 2021 12:46:26 GMT

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)

Re: Cross table/Distinct Sum

KD-Qlik — Fri, 06 Aug 2021 14:13:29 GMT

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)