Dynamic Report with Ranking

user069636
user069636 Member
in Magic ETL

Hi, I wanted to create a report that shows sales rank of each product by Store. However, because there are more than one transaction for each model, there are multiple rows for the same product in the dataset. As a result, I have multiple ranking numbers for the same model, when I use the rank & window feature in ETL. Is there a way that I can group the products so that the report sums all transactions of a product model by store and ranks the models by the sum? 

Comments

  • GrantSmith
    GrantSmith Coach

    Hi @user069636 

     

    Try using a Group By tile before your Rank & Window tile to group by the product and store and use the SUM aggregation on your necessary fields

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user069636
    user069636 Member

    @GrantSmith It looks like the Group By function drops all other columns not being used. Is there a way to still have the unused columns after grouping by? 

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    the window function isn't super great for these percent of total type calcs.  the design pattern @GrantSmith is recommending is calculate your categegory totals with a group by, rank the subtotal, then JOIN the results back to your main dataset.

     

    This is potentially unweildy so you could implement this design in a dataset view, beta feature, talk to your CSM, JOINED back to the source data.

     

    alternatively... just do it in analyzer.  rank() over (partition by store order by sum(amount)).  if you don't have the feature switch enabled in your instance talk to your CSM.

    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"
  • Derreck
    Derreck Member

    @jaeW_at_Onyx is there a way define the order by to descending? Appears too default to ascending?



    thank you!

  • Derreck
    Derreck Member

    never mind! Found it!


    rank() over (order by sum(`amount`)desc)

Categories