Percentage by cat by month

[Tenuki]

Hello,

I would to show the distinct number of customer by a category of product, for 2 categories, and have it by month.

So far I have :

The distinct id for both cat with 

Count(DISTINCT {<CatProductID= {'Mycat1'}>} ID_Client)

Count(DISTINCT {<CatProductID= {'Mycat2'}>} ID_Client)

But when I try to find the percentage of client by month, I get the percentage of total client of the cat global, not for the month.

I try this : 
Count(DISTINCT {<CatProductID= {'Mycat1'}>} ID_Client) / (Count(DISTINCT {<CatProductID= {'Mycat1','Mycat2'}>} TOTAL {<MonthName>} ID_Client))

Maybe TOTAL need to be change ? 

Thanks for your help 

[Tenuki]

I found my error 

Correction :

Count(DISTINCT {<CatProductID= {'Mycat1'}>} ID_Client) /

(Count(DISTINCT {<CatProductID= {'Mycat1','Mycat2'}>} ID_Client))

But I have some client that buy both of my cat on the same month. So my percentage isn't right.

Is there a way to work on this issue ?

[marcus_sommer · Accepted solution]

It depends always on which kind of rate should be calculated. Maybe the following are alternatives for your scenario:

rangesum(Count(DISTINCT {<CatProductID= {'Mycat2'}>} ID_Client),
                 Count(DISTINCT {<CatProductID= {'Mycat1'}>} ID_Client))

Count(DISTINCT {<CatProductID= {'Mycat1','Mycat2'}>} ID_Client&CatProductID)

[Tenuki]

Thanks, rangesum give me want I expect.