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.1K Product Ideas
 1.1K Ideas Exchange
 1.2K Connect
 969 Connectors
 256 Workbench
 Cloud Amplifier
 1 Federated
 2.4K Transform
 76 SQL DataFlows
 500 Datasets
 1.8K Magic ETL
 2.7K Visualize
 2.2K Charting
 369 Beast Mode
 19 Variables
 483 Automate
 101 Apps
 378 APIs & Domo Developer
 6 Workflows
 22 Predict
 6 Jupyter Workspaces
 16 R & Python Tiles
 316 Distribute
 64 Domo Everywhere
 252 Scheduled Reports
 59 Manage
 59 Governance & Security
 1 Product Release Questions
 5K Community Forums
 37 Getting Started
 23 Community Member Introductions
 63 Community Announcements
 4.8K Archive