Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
kaygee28
Contributor III
Contributor III

Two Charts Not Displaying Similar Values

Good Day I've got two charts whereby one calculates the total sales for each month beginning 2015 to Jan 2016. I've also got a second chart that divides the sales into quarters however the values from the months don't correspond to the values for each quarter.

The problem might be that I am selecting distinct values from the three sheets I am loading onto the script, when I remove the distinct function from the expression the two charts correspond correctly is there a way to fix this?

Thanks in advance

The expression for the first chart is:

Count(DISTINCT{$<[Year Purchased]={"*"}-{'Unknown'},[Month Purchased]={"*"}-{'Unknown'}>}[Contact ID])

and the expression for the second chart is:

Count(DISTINCT{$<Year={2015,2016}-{'Unknown'},Quarter={"*"},[Month Purchased]={"*"}>}[Contact ID])

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Yes. I a contact id has a purchase in two months of a quarter it will appear in two months. If you count those two occurrences you're still dealing with one unique contact id, not two. So a count distinct over a quarter will show only 1 unique contact id. You need to decide exactly what you want to count. If you want a sum of the monthly counts then you can do that with this:

sum(aggr(Count(DISTINCT{$<[Year Purchased]={"*"}-{'Unknown'},[Month Purchased]={"*"}-{'Unknown'}>}[Contact ID]),[Month Purchased]))


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

Yes. I a contact id has a purchase in two months of a quarter it will appear in two months. If you count those two occurrences you're still dealing with one unique contact id, not two. So a count distinct over a quarter will show only 1 unique contact id. You need to decide exactly what you want to count. If you want a sum of the monthly counts then you can do that with this:

sum(aggr(Count(DISTINCT{$<[Year Purchased]={"*"}-{'Unknown'},[Month Purchased]={"*"}-{'Unknown'}>}[Contact ID]),[Month Purchased]))


talk is cheap, supply exceeds demand
kaygee28
Contributor III
Contributor III
Author

Gysbert it worked, but I still do not understand the concept however it works perfectly, I just need to understand this so that I can apply the methodology correctly going forward.

kaygee28
Contributor III
Contributor III
Author

The reason why I am asking is that I had a similar table not so long ago and the same expression worked and all the quarterly results corresponded to the yearly sales.

Gysbert_Wassenaar

See this blog post: AGGR... and the accompanying technical brief document: QlikView Technical Brief - AGGR.docx


talk is cheap, supply exceeds demand
Anonymous
Not applicable

Count(DISTINCT{$<[Year Purchased]={"*"}-{'Unknown'},[Month Purchased]={"*"}-{'Unknown'}>}[Contact ID])

In similar way to quarter also