topic Re: Count Distinct per years in QlikView

Count Distinct per years

gab_cerelli — Fri, 23 Apr 2021 09:48:25 GMT

Hi guys,

I've a sales database listed per date, location, season, ecc...

All I wanted is to do a count distinct that returns me only last year active stores, I tried with COUNT (DISTINCT ('STORES') with year as dimension but it doesnt' return me the correct values, how can I add more filters to this query? Maybe I've to introduce a new variable like Max_Year what do you think about it?

Thank you

Gabriele

Re: Count Distinct per years

MayilVahanan — Fri, 23 Apr 2021 10:10:11 GMT

Hi @gab_cerelli

Try like below

Count({<Year={$(=Max(Year)-1)}>}DISTINCT Store)

$(=Max(Year)-1) <-- it will give ur max year -1 values.

for ex: Max year is 2021, it will give 2020 as result

Re: Count Distinct per years

gab_cerelli — Fri, 23 Apr 2021 10:18:21 GMT

Thanks so much for your answer Mayil, it seems working fine, what if I want to add more filters to this count?

Re: Count Distinct per years

MayilVahanan — Fri, 23 Apr 2021 10:22:56 GMT

Hi @gab_cerelli

You can add more filter with comma separator like

Count({<Year={$(=Max(Year)-1)}, Country={'India', 'Singapore'}>}DISTINCT Store)

Re: Count Distinct per years

gab_cerelli — Fri, 23 Apr 2021 12:57:58 GMT

Thanks my friend, you're the best!