Sum of Distinct Count Variance
Hi
I need help on below beast mode not working. I am trying to get the sum of all the variance using distinct count of customers. I am trying to get my total points of distribution gained. Thank you in advance.
SUM(COUNT(DISTINCT
CASE WHEN `Year`= 2020 AND `Month #` <=`Month # for Today`
THEN `Customer Name`
END
))

SUM(COUNT(DISTINCT
CASE WHEN `Year`= 2019 AND `Month #` <=`Month # for Today`
THEN `Customer Name`
END
))
Best Answer

So ... while window functions are amazing for many things, it won't work when you're trying to do a count distinct.
why?
SUM(
COUNT(DISTINCT
CASE WHEN `Year`= YEAR(CURRENT_DATE()) AND `Month #` <=`Month # for Today` THEN `Customer Name`
END
))
OVER ()keep in mind what is happening under the hood. first Domo does a COUNT(DISTINCT) of customer names, for whatever is on the Axis. (set of items)
then it adds up all the results.
so if i had item 1 = 15 distinct customers, item2 = 12 distinct customers and item3 = 31 distinc customers, i can't just sum 15,12,31... because presumeably i have overlap in the customers who bought items.
SOLUTION
APPEND your data onto itself (UNION) but change the Item number in the second UNION to be a constant 'Total Items'.
now you can do a basic count(distinct customer) and you'll have a 'Total Items' ... item.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Answers

Hi @user033690
You might be able to solve your problem using a windowing function however I'd recommend restructuring your data. I've done a previous write up regarding customized period over period data models. You can refer to https://dojo.domo.com/t5/CardBuilding/showpercentonperiodoverperiodgraph/mp/50540/highlight/... for an in dept description of what needs to happen.
The basics being you'd have a period type column for Current and Last Year (which you'd filter on just these two) which you can then use a beast mode to calculate the total for that period and then subtract the two numbers. I'd highly recommend this method as it gives you a lot more flexibility in your analysis.
If you must use a beast mode you could try something like:
SUM(COUNT(DISTINCT CASE WHEN `Year`= YEAR(CURRENT_DATE()) AND `Month #` <=`Month # for Today` THEN `Customer Name` END )) OVER ()  SUM(COUNT(DISTINCT CASE WHEN `Year`= YEAR(CURRENT_DATE())1 AND `Month #` <=`Month # for Today` THEN `Customer Name` END )) OVER ()
I've also replaced 2020 and 2019 with YEAR(CURRENT_DATE()) functions to automatically calculate the current year and last year (hence the 1) so you don't have to worry about changing the year value next year and in future years. (Unless you specifically want 2020 and 2019 then use those values)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
Thank you for the response Grant.
I tried the BM you suggested. unfortunately it gives me a different result. Please review the table below. I am looking to get the FY20FY19 variance both by item and as a total. I hope you can help me figuring this out. Thank you in advance.
Item Number FY20 Customer Count FY19 Customer Count BM result Should be result 1012B 96 84 89 12 1042B 91 83 89 8 1044 90 84 89 6 1022B 80 69 89 11 1014B 67 64 89 3 1092B 58 52 89 6 Total 482 436 534 46 0 
So ... while window functions are amazing for many things, it won't work when you're trying to do a count distinct.
why?
SUM(
COUNT(DISTINCT
CASE WHEN `Year`= YEAR(CURRENT_DATE()) AND `Month #` <=`Month # for Today` THEN `Customer Name`
END
))
OVER ()keep in mind what is happening under the hood. first Domo does a COUNT(DISTINCT) of customer names, for whatever is on the Axis. (set of items)
then it adds up all the results.
so if i had item 1 = 15 distinct customers, item2 = 12 distinct customers and item3 = 31 distinc customers, i can't just sum 15,12,31... because presumeably i have overlap in the customers who bought items.
SOLUTION
APPEND your data onto itself (UNION) but change the Item number in the second UNION to be a constant 'Total Items'.
now you can do a basic count(distinct customer) and you'll have a 'Total Items' ... item.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Categories
 All Categories
 1.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 285 Workbench
 4 Cloud Amplifier
 5 Federated
 2.7K Transform
 91 SQL DataFlows
 575 Datasets
 2.1K Magic ETL
 3.5K Visualize
 2.4K Charting
 619 Beast Mode
 24 App Studio
 33 Variables
 609 Automate
 155 Apps
 421 APIs & Domo Developer
 31 Workflows
 2 DomoAI
 29 Predict
 12 Jupyter Workspaces
 17 R & Python Tiles
 372 Distribute
 103 Domo Everywhere
 266 Scheduled Reports
 3 Software Integrations
 99 Manage
 96 Governance & Security
 4 Domo University
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 91 Community Announcements
 4.8K Archive