Dynamic Report with Ranking

user069636

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? 

GrantSmith

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

user069636

@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

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.

Derreck

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

thank you!

Derreck

never mind! Found it!

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