Beast mode formula: Case When with distribution points

Jelle_Smits · 2022-04-08T14:29:44+00:00

There was an error rendering this rich post.

Hi,

I have a question about my beast mode formula concerning a distribution calculation.

Currently, I am calculating the distribution points based on the volume. If one product of brand x has been sold in period y, then one customer gets 1 distribution point. It is max 1 distribution point per customer per brand.

I have made the beast mode formula to support this:

count(distinct case when `Volume` > 0 then CONCAT(`CUSTOMER_NUMBER`,`SubBrand`)end)

This formula works great. However We want more insight in the distribution points of the brands and sub brands. For brand A, we would like it to be calculated on the combination of customer number and brand and for brand B, we would like it to be calculated on the combination of customer number and sub brand. So I made the following beast mode formula:

CASE

WHEN `BRAND` = 'A' THEN count(distinct case when `Volume` > 0 then CONCAT(`CUSTOMER_NUMBER`,`BRAND`)end)

WHEN `BRAND` = 'B' THEN count(distinct case when `Volume` > 0 then CONCAT(`CUSTOMER_NUMBER`, `SubBrand`)end)

END

Unfortunately, this formula does not work. Any ideas on how to solve this?

Thanks for helping :)

Find more posts tagged with

Beast Mode

Accepted answers

MarkSnodgrass

Try moving your count distinct to the very outside like this:

count(distinct 
(CASE

 WHEN `BRAND` = 'A' THEN 
 	(case when `Volume` > 0 then CONCAT(`CUSTOMER_NUMBER`,`BRAND`)end)

 WHEN `BRAND` = 'B' THEN 
 	(case when `Volume` > 0 then CONCAT(`CUSTOMER_NUMBER`, `SubBrand`)end)

 END))

All comments

MarkSnodgrass

Try moving your count distinct to the very outside like this:

count(distinct 
(CASE

 WHEN `BRAND` = 'A' THEN 
 	(case when `Volume` > 0 then CONCAT(`CUSTOMER_NUMBER`,`BRAND`)end)

 WHEN `BRAND` = 'B' THEN 
 	(case when `Volume` > 0 then CONCAT(`CUSTOMER_NUMBER`, `SubBrand`)end)

 END))

Jelle_Smits

Aah yes, that works great! Thank you very much :)