Nested Beast mode
Hi everyone,
I have some data like companyid, journeytype, ratingid and npsRating. With ratingid and npsRating, I usually calculate NPS (net promoting score) in beast mode like this:
(((Count(DISTINCT (CASE WHEN ((`npsrating` >= 9) AND (`npsrating` < 11)) THEN `ratingid` END ))  Count(DISTINCT (CASE WHEN (`npsrating` <= 6) THEN `ratingid` END ))) / count(DISTINCT `ratingid`)) * 100)
Now, my manager asked me to calculate NPS for each company, and then take the average over the NPS_percompany for each journeytype.
To solve this I know that I can put a window function and calculate NPS like this:
AVG((((Count(DISTINCT (CASE WHEN ((`npsrating` >= 9) AND (`npsrating` < 11)) THEN `ratingid` END ))  Count(DISTINCT (CASE WHEN (`npsrating` <= 6) THEN `ratingid` END ))) / count(DISTINCT `ratingid`)) * 100)) OVER (PARTITION BY `journeytype`)
as long as companyid exist in the groupby, the calculated amount for npe_per_journeytype is correct:
but as I wanna present it in a bar chart, I have to take out the companyId, and when it goes out from groupby, nps will be calculated for each journeytype, not per company and then take the average on journeytype:
What crossed my mind what that to have nested Beast mode as bellow:
AVG(AVG((((Count(DISTINCT (CASE WHEN ((`npsrating` >= 9) AND (`npsrating` < 11)) THEN `ratingid` END ))  Count(DISTINCT (CASE WHEN (`npsrating` <= 6) THEN `ratingid` END ))) / count(DISTINCT `ratingid`)) * 100)) OVER (PARTITION BY `companyid`, `journeytype` )) OVER (PARTITION BY `journeytype`)
but it does not work:
I cannot calculate this NPS in sql, because filters should apply on it on the fly, for example, companyType, filterdate, ... should be chose by the user.
Do you have any idea how I can tackle this problem?
Comments

You' can't have a window function inside a window function. Have you tried partitioning by the company Id and then the journey type since it sounds like you're wanting to break out your average NPS by both of those categories.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
Yes, but it did not solve our problem unfortunately.
0
Categories
 11K All Categories
 5 Private Company Board
 2 APAC User Group
 12 Welcome
 42 Domo News
 9.9K Using Domo
 2K Dataflows
 2.5K Card Building
 2.2K Ideas Exchange
 1.2K Connectors
 349 Workbench
 267 Domo Best Practices
 12 Domo Certification
 474 Domo Developer
 51 Domo Everywhere
 110 Apps
 726 New to Domo
 85 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 29 お知らせ
 65 Kowaza
 302 仲間に相談
 664 ひらめき共有