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
 10.5K All Categories
 6 Connect
 916 Connectors
 250 Workbench
 460 Transform
 1.7K Magic ETL
 69 SQL DataFlows
 476 Datasets
 187 Visualize
 251 Beast Mode
 2.1K Charting
 11 Variables
 16 Automate
 354 APIs & Domo Developer
 88 Apps
 3 Workflows
 20 Predict
 5 Jupyter Workspaces
 15 R & Python Tiles
 245 Distribute
 62 Domo Everywhere
 242 Scheduled Reports
 21 Manage
 42 Governance & Security
 170 Product Ideas
 1.2K Ideas Exchange
 10 Community Forums
 27 Getting Started
 14 Community Member Introductions
 55 Community News
 4.5K Archive