5 Replies Latest reply: Apr 20, 2018 7:12 AM by Sunny Talwar

# Dimension Set Analysis? Maximum week for a table

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
• ###### Re: Dimension Set Analysis? Maximum week for a table

hi

may be this

dimension

Store

mesure

=Sum({\$<Week={\$(=max(Week))}>}Sales)

• ###### Re: Dimension Set Analysis? Maximum week for a table

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.

• ###### Re: Dimension Set Analysis? Maximum week for a table

If a certain store doesn't have sales in the max week... like this

 1 Week Store Sales 1 Atlanta 3 1 Austin 5 1 Boston 8 1 Chicago 5 1 Philadelphia 9 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))

• ###### Re: Dimension Set Analysis? Maximum week for a table

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,

• ###### Re: Dimension Set Analysis? Maximum week for a table

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