topic Last 6 weeks in QlikView

Last 6 weeks

Fri, 17 Jun 2016 10:12:06 GMT

I have a week dimension like below which is actually in a sql script at the back end. I have created a bar chart using that week dimension which is bringing all weeks on x-axis. But I want to see only last 6 weeks on the x-axis dynamically. Is there any way I can write an expression on "Add Calculated Dimension" under Dimension tab in Chart properties?

Weekstart

04 Jan 16

11 Jan 16

18 Jan 16

06 Jun 16

SQl script: convert(varchar(11),DATEADD(wk, DATEDIFF(wk,0,Createdate), 0),6) as Weekstart

Thanks

Re: Last 6 weeks

sunny_talwar — Fri, 17 Jun 2016 10:14:55 GMT

May be use set analysis to your existing expression instead of a calculated dimension:

{<Weekstart = {"$(='>=' & Date(Max(Weekstart) - 42, 'DD MMM YY') & '<=' & Date(Max(Weekstart), 'DD MMM YY'))"}>}

Re: Last 6 weeks

Anonymous — Fri, 17 Jun 2016 10:22:26 GMT

Hello Sunny

I guess it should be 6 instead of 42.

{<Weekstart = {"$(='>=' & Date(Max(Weekstart) -6, 'DD MMM YY') & '<=' & Date(Max(Weekstart), 'DD MMM YY'))"}>}

Thanks & Regards

Chintan

Re: Last 6 weeks

sunny_talwar — Fri, 17 Jun 2016 10:33:40 GMT

I think it needs to be 42 because I am going back 42 days back and not 6 weeks back in my expression

Re: Last 6 weeks

Fri, 17 Jun 2016 10:47:09 GMT

Thanks for your replies. I don't think it will work. I want to restrict Weekstart at chart level only. In other words, I want to bring all weeks from backend and show only last 6 weeks data in bar chart. Any other alternative?

Thanks

Re: Last 6 weeks

sunny_talwar — Fri, 17 Jun 2016 10:49:39 GMT

The proposed solution is doing exactly that my friend. I am not using a where clause in the script. This is set analysis for chart expression to restrict it to show only last 6 week's worth of data