topic Re: Two Charts Not Displaying Similar Values in QlikView

Two Charts Not Displaying Similar Values

kaygee28 — Tue, 23 Feb 2016 09:50:24 GMT

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

Re: Two Charts Not Displaying Similar Values

Gysbert_Wassenaar — Wed, 24 Feb 2016 16:42:56 GMT

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

Re: Two Charts Not Displaying Similar Values

kaygee28 — Wed, 24 Feb 2016 20:02:10 GMT

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.

Re: Two Charts Not Displaying Similar Values

kaygee28 — Wed, 24 Feb 2016 21:00:42 GMT

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.

Re: Two Charts Not Displaying Similar Values

Gysbert_Wassenaar — Thu, 25 Feb 2016 06:29:32 GMT

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

Re: Two Charts Not Displaying Similar Values

Anonymous — Thu, 25 Feb 2016 07:15:53 GMT

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

In similar way to quarter also