topic Re: Max Week in Dimension in QlikView

Max Week in Dimension

Anonymous — Thu, 20 Jul 2017 12:17:00 GMT

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

Re: Max Week in Dimension

zhadrakas — Fri, 21 Jul 2017 06:29:36 GMT

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

Re: Max Week in Dimension

Anonymous — Fri, 21 Jul 2017 08:16:04 GMT

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?

Re: Max Week in Dimension

zhadrakas — Fri, 21 Jul 2017 10:25:23 GMT

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

Re: Max Week in Dimension

Anonymous — Fri, 21 Jul 2017 10:41:09 GMT

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?

Re: Max Week in Dimension

zhadrakas — Sat, 22 Jul 2017 10:00:07 GMT

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

Re: Max Week in Dimension

Anonymous — Wed, 26 Jul 2017 16:30:15 GMT

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)

Re: Max Week in Dimension

zhadrakas — Thu, 27 Jul 2017 06:45:32 GMT

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