Last 4 Weeks

[mparker123]

Hello All,

I am creating a line graph by weeks. In order for me to do this I had to create a date field based on the fields I already had. The expression is as follows:  =Week(MakeDate(YEAR,MONTH,DAY_NUMBER))

That works and I created week numbers however in the line graph I only want to show the last 4 weeks. How would I add this to my expression above?

[Anil_Babu_Samineni]

Make be this?

In script, you need to create week field like

Week(MakeDate(YEAR,MONTH,DAY_NUMBER)) as Week

And Measure, as follow

Sum({<Week = {">=$(=Max(Week-4)) <= $(=Max(Week))"}>} Measure)

[vikasdesai]

hello

first create Week Field

Week(MakeDate(year,month,day_number)) as Week

Use Following Expression

Sum({<Week = {">=$(=Max(Week-3)) <= $(=Max(Week))"}>} Measure)

[hic · Accepted solution]

Or, alternatively

If(

   WeekStart(Today())-WeekStart(MakeDate(YEAR,MONTH,DAY_NUMBER))<28,

   Week(MakeDate(YEAR,MONTH,DAY_NUMBER)),

   Null()

   )

HIC

[mparker123]

Thank you Henric as the expression works. However, I would now like to take this a step further and only show the top 10 lets say of this. How would i incorporate the RANK function into this expression?

[hic]

First, to show the top 10 can easily be done by using "Dimension Limiits" (third tab in the bar chart properties in QlikView). This is the easiest way to solve the problem.

Alternatively, you can use Rank(). Note however that my formula defines a field or a dimension, so it can be used either in the script or directly as a chart dimension. Rank(), however, is something that is calculated, so it can only be used in the measure.

But you can combine my previous formula with the following as measure:

If(Rank(Sum(Amount))<=10,Sum(Amount))

HIC