Two Charts Not Displaying Similar Values

[kaygee28]

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])

[Gysbert_Wassenaar · Accepted solution]

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]))

[kaygee28]

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]

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

[Anonymous]

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

In similar way to quarter also