How to use count distinct

user14340
user14340
in Dataflows

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

  • ST_-Superman-_
    ST_-Superman-_ 🟤
    Answer ✓

    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
    ______________________________________________________________________________________________
  • DataMaven
    DataMaven 🟢
    Answer ✓
    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"

Answers

  • user14340
    user14340

    @DataMaven , @ST_-Superman-_ Both of these worked, thank you!

  • DataMaven
    DataMaven 🟢

    @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"
  • DaniBoy
    DaniBoy

    domo

    💎

    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.
  • DataMaven
    DataMaven 🟢

    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"
  • DaniBoy
    DaniBoy

    domo

    💎

    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.

Categories