topic Re: Previous 8 Weeks on a Chart (Variables) in QlikView

Previous 8 Weeks on a Chart (Variables)

Thu, 19 Feb 2015 10:33:05 GMT

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

Re: Previous 8 Weeks on a Chart (Variables)

AbhijitBansode — Thu, 19 Feb 2015 11:01:06 GMT

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

make sure you have same format for MaxDate and Prior8WeekPeriod.

Re: Previous 8 Weeks on a Chart (Variables)

Thu, 19 Feb 2015 11:20:58 GMT

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

It gives me a 'no data to display'

Re: Previous 8 Weeks on a Chart (Variables)

AbhijitBansode — Thu, 19 Feb 2015 11:36:05 GMT

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)

Re: Previous 8 Weeks on a Chart (Variables)

Thu, 19 Feb 2015 11:40:23 GMT

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

Re: Previous 8 Weeks on a Chart (Variables)

jonathandienst — Thu, 19 Feb 2015 12:06:55 GMT

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

Re: Previous 8 Weeks on a Chart (Variables)

jonathandienst — Thu, 19 Feb 2015 12:17:43 GMT

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!

Re: Previous 8 Weeks on a Chart (Variables)

Thu, 19 Feb 2015 12:28:29 GMT

Thank you, works well!!