Count Distinct per years

[gab_cerelli]

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

[MayilVahanan]

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

[gab_cerelli]

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

[MayilVahanan · Accepted solution]

Hi @gab_cerelli 

You can add more filter with comma separator like

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

[gab_cerelli]

Thanks my friend, you're the best!