Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to create a table which will always just show sales for stores for the latest week. Is there anyway to use set analysis on my dimension to only show the sum of sales per store for the maximum of the week #?
An example of the raw data is below:
Week | Store | Sales |
1 | Atlanta | 3 |
1 | Austin | 5 |
1 | Boston | 8 |
1 | Chicago | 5 |
1 | Philadelphia | 9 |
2 | Atlanta | 6 |
2 | Austin | 1 |
2 | Boston | 7 |
2 | Chicago | 1 |
2 | Philadelphia | 1 |
hi
may be this
dimension
Store
mesure
=Sum({$<Week={$(=max(Week))}>}Sales)
hi
may be this
dimension
Store
mesure
=Sum({$<Week={$(=max(Week))}>}Sales)
If a certain store doesn't have sales in the max week... like this
Week | Store | Sales |
1 | Atlanta | 3 |
1 | Austin | 5 |
1 | Boston | 8 |
1 | Chicago | 5 |
1 | Philadelphia | 9 |
1 | New York | 10 |
---|---|---|
2 | Atlanta | 6 |
2 | Austin | 1 |
2 | Boston | 7 |
2 | Chicago | 1 |
2 | Philadelphia | 1 |
and you would like to get the sales for New York 1st week because that is it's max week... then try this
FirstSortedValue(Aggr(Sum(Sales), Store, Week), -Aggr(Week, Store, Week))
Thanks Bruno, I was hoping there was some way to use set Analysis on a dimension, but I suppose using the set analysis on all the measures will work as well.
Sunny,
If you don't mind, can you explain what the -Aggr(Week, Store, Week) part of the equation is accomplishing?
FirstSortedValue(Aggr(Sum(Sales), Store, Week), -Aggr(Week, Store, Week))
Thanks,
That is helping the expression pick the most recent week per each store. As I pointed in my expression, New York didn't have any data for week 2... for New York the max week is 1. So, if you want to show New York's sale for week 1, you can use FirstSortedValue expression rather than using set analysis.
Best,
Sunny