Max Week in Dimension

[Anonymous]

My file contains a list of weeks which can be filtered and I want a bar chart to display the sales for the max week. Does anyone know of a way? I have tried various solutions involving the use of Max.

I saw Diana's thread and tried Ramon's suggestion of =aggr(max(WEEK),$Field) but this is just returns a '-' in the chart. I checked and there are no dashes or null values in the Week data so not sure why this is appearing.

The Field is called Week and these are displayed as YYYYWW e.g. 201726

[zhadrakas]

Create a variable

maxWeek

=max(Week)

then try this in Dimension

=if($(maxWeek)=Week, Week)

tick the Checkbox "suppress when value is null"

regards

tim

[Anonymous]

Hi Tim,

This works, thank you! I may need to create a new discussion for this but what I really want is for the bar chart to always show 5 weeks of data with the most recent week being the max of whatever is selected from the filter (or the max week if nothing is selected). Do you know if this is possible?

[zhadrakas]

you could

=if(Week >= $(maxWeek)-5, Week)

tick the Checkbox "suppress when value is null".

This way you always get the last 5 weeks. If you select Week 21. you will get week 17-21

Think about using WeekYear Field that you can Display week 51,52,1,2,3 if needed

regards

tim

[Anonymous]

Hi Tim,

Thanks again. This works but if I select a single week then only a single week is displayed on the chart. Using this method I always need to have at least 5 weeks selected on the filter. Is it possible to display all 5 weeks with only 1 week selected?

[zhadrakas · Accepted solution]

then you just use Week as Dimension and try this Expression

Sum({$<Week+={">$(=FiveWeeksAgo) <=$(=maxWeek)"}>}Sales)

Variables:

maxWeek:           =max(Week)

FiveWeeksAgo:   =max(Week)-5

regards

tim

[Anonymous]

Tim, this is fantastic! Final expression is below, the variable and field names are slightly different and I added an extra bit for when nothing is selected in the filter as this would select more than 5 weeks by default.

Expression in Measure

=if(isnull(getfieldselections(Week)),sum([Total Sales]),Sum({$<Week+={">$(=vWeekPeriod) <=$(=vMaxWeek)"}>} [Total Sales]))

Expression in Dimension

=if(isnull(getfieldselections(Week)),if($(vMaxWeek)-4<=Week,Week),Week)

[zhadrakas]

I'm Glad i could help. Please mark an answer as correct to Close this thread.