How to use count distinct

user14340
user14340 Member
in Magic ETL

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-_ Coach
    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 Coach
    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
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach
    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 Coach
    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
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user14340
    user14340 Member

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

  • DataMaven
    DataMaven Coach

    @DaniBoy  - Superman and I have another dual solution - would you mind adding a solution for him, too?  Thanks!

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • [Deleted User]
    [Deleted User] Member

    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?

  • DataMaven
    DataMaven Coach

    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
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • [Deleted User]
    [Deleted User] Member

    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!

Categories