Previous 8 Weeks on a Chart (Variables)

[#1]

Hello,

I am trying to create my charts to show the last 8 weeks of data by using variables.

I have created the following variables:

So on a chart I want to see the weeks between these dates ie - 8 weeks of data. (I want to avoid using the dimension limits as I have a view to selecting the number of weeks to be seen on the chart ie 12, 16 etc.)

I have used this expression on my chart:

=SUM({$<DATE={'$(vPrior8WeekPeriod)'}>}[Amount])

However it plots just that date (25/11/2014 in this case). I am looking for the 8 weeks between the 25th & 20th.

Thanks in advance

Joe

[AbhijitBansode]

=SUM( {<DATE={'>= $(vPrior8WeekPeriod) <= $(vMaxDate)'}>} [Amount] )

make sure you have same format for MaxDate and Prior8WeekPeriod.

[#3]

I have changed the formats to match, but it hasn't quite worked yet:

It gives me a 'no data to display'

[AbhijitBansode]

QlikView considers value in variable as string not as a date, you will need to convert that string into date and hten only you can compare that value with DATE. Something like below:

Sum( {<DATE={">= $(=Date#(vPrior8WeekPeriod,'DD-MM-YYYY')) <= $(=Date#(vMaxDate,'DD-MM-YYYY'))"} >} Amnt)

[#5]

Try this,

Try to create like below

WeekStart={'>=$(=Date(vWeekSelect)) <=$(=Date(vWeekSelect-8*7))'},

vWeekSelect =

=Weekstart(Date(if((GetFieldSelections(Year)=Year(Today()) AND GetSelectedCount(Month)=0 AND GetSelectedCount(Qtr_No)=0

AND GetSelectedCount(DATE)=0 AND GetSelectedCount(WeekStart)=0

),Today(),Min(DATE))),0,-1)

Regards,

Sreeharsha V

[jonathandienst]

Hi

Assuming all the date formats are the same, then this will work:

SUM({<DATE={">=$(vPrior8WeekPeriod) <=$(vMaxDate)"}>} [Amount])

Do not include extra spaces in the set expression.

HTH

Jonathan

[jonathandienst · Accepted solution]

I nearly forgot - define the variables as:

vMaxDate:                       =Date(Max(DATE))

vPrior8WeekPeriod:          =Date(RangeMax(vMaxDate - 56, Min({1} DATE)))

Note the = sign is part of the variable!

[#8]

Thank you, works well!!