topic Re: Last 4 Weeks in QlikView

Last 4 Weeks

mparker123 — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Last 4 Weeks

Anil_Babu_Samineni — Wed, 18 Oct 2017 01:25:38 GMT

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)

Re: Last 4 Weeks

vikasdesai — Wed, 18 Oct 2017 05:23:51 GMT

hello

first create Week Field

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

Use Following Expression

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

Re: Last 4 Weeks

hic — Wed, 18 Oct 2017 11:03:07 GMT

Or, alternatively

If(

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

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

Null()

)

HIC

Re: Last 4 Weeks

mparker123 — Wed, 18 Oct 2017 14:24:04 GMT

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?

Re: Last 4 Weeks

hic — Wed, 18 Oct 2017 14:56:02 GMT

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