Beast mode formula: Case When with distribution points
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 :)
Best Answer
-
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))
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
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))
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Aah yes, that works great! Thank you very much :)
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive