How to use count distinct
When I do count(distinct) it is saying that is not a valid formula. Below is my formula. I am counting the sales names named (SalesHost), but against their sales and I want to sum the sales. I added a beastmode to calculate the rank, but need to get rid of the duplicate sales names. The first is invalid, but the second is not and the second wont allow aggregation.
count(distinct `SalesHost`) over (order by `NetSales` desc)
count(`SalesHost`) over (order by `NetSales` desc)
Best Answers
-
I answered this in another chain...
@user14340 - Can you provide some sample data so that I can test it out with your schema?
I'm not sure what is causing the issue. I might try
count(`SalesHost`) over (order by sum(`NesSales`) desc)
but i'm not sure if that is valid or not.
@user14340 also commented that the rank() function worked better for what he was wanting to do
______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________3 -
This may seem crazy, but try adding a SUM. I've had that do the trick on beast modes not working.
SUM(count(distinct `SalesHost`)) over (order by `NetSales` desc)DataMaven
Breaking Down Silos - Building Bridges
Check out my video!
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Answers
-
@DataMaven , @ST_-Superman-_ Both of these worked, thank you!
1 -
@DaniBoy - Superman and I have another dual solution - would you mind adding a solution for him, too? Thanks!
DataMaven
Breaking Down Silos - Building Bridges
Check out my video!
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
Great collaboration @DataMaven and @ST_-Superman-_
As far as I can tell all of the suggestions you both provided in the thread are marked as solutions. DId I miss something?
Dani aka "Mr.Dojo"
Dojo Admin
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
**You can update your Dojo Community name and avatar by clicking on your avatar then the "My Profile" button.1 -
They weren't earlier - thought that was something you had to do, and wanted to make sure that @ST_-Superman-_ got his due! ?
DataMaven
Breaking Down Silos - Building Bridges
Check out my video!
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Kudos to @user14340 who has the power to mark the solution as the original poster.
You can update your user profile (User Name and Avatar) here.
Thanks!
Dani aka "Mr.Dojo"
Dojo Admin
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
**You can update your Dojo Community name and avatar by clicking on your avatar then the "My Profile" button.0
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 38 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 258 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 105 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有