Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)