How can I turn my Count (Distinct If into a set expression?

[robinwiman]

Is it possible to turn the Count (Distinct expression into a set?

I really hate the performance drainer that count distinct is.

Here is an expression:

Count (Distinct if(Counter='1'and Shop <> '1101' and _YTD_WTY='1',Kvitto))

I'm thinking:

Count ( {< Counter = {'1'}, Shop -= {'1101'} >} Kvitto*_YTD_WTY)

The thing is that when I use the set expression (which works btw) my data changes. I use the formula to calculate number of transactions (Kvitto translates into Receipt in Swedish). So the whole expression is then later divided by visitors.

I can see the changes are small but nonetheless there. Some results change from 2.3% to 2.4% and similar.

Where am I going wrong with this?

[Anil_Babu_Samineni]

This this way?

Count ( {< Counter = {'1'}, Shop -= {'1101'}, _YTD_WTY={1} >} Kvitto)

[sunny_talwar · Accepted solution]

Are you may be missing a DISTINCT?

Count(DISTINCT {< Counter = {'1'}, Shop -= {'1101'}>} Kvitto*_YTD_WTY)

[robinwiman]

Yeah, this works!

So just a follow-up question; how would the two expressions compare performance-wise? Isn't Count(DISTINCT quite heavy to calculate ? Albeit the removed If-expression should do its part.

[sunny_talwar]

It is a myth as suggested by HIC

A Myth About Count(distinct …)

[robinwiman]

Exactly what I needed thanks Sunny.