topic Current plus previous 13 weeks in QlikView

Current plus previous 13 weeks

richnorris — Thu, 24 Apr 2014 14:27:37 GMT

Hi,

So, I'm trying to create a chart that shows the current week plus previous 13 weeks. The dashboard contains more years than just the current year, but thats cool, I want to show data that is in those weeks for previous years. I'm trying to use set analysis, but its not working quite right. Anyone know whats wrong with the below? Its showing 15 weeks for some reason.

Sum({<Week ={">$(=num(Week(Today())-13))"}, Week ={"<=$(=num(Week(Today())))"}>} [$ VA])

Re: Current plus previous 13 weeks

its_anandrjs — Thu, 24 Apr 2014 14:33:52 GMT

What about some thing like

='W'&Sum({<Week ={">$(=num(Week(Today())-13))"} >} [$ VA])

Re: Current plus previous 13 weeks

jonasheisterkam — Thu, 24 Apr 2014 14:40:58 GMT

I would use a nother expression, week gives only the week in the year. Use st like Datefiled >= floor(weekstart(today-13*7))

Re: Current plus previous 13 weeks

richnorris — Thu, 24 Apr 2014 14:46:02 GMT

What do you mean when you say it gives 'only the week in the year' ? If I select week 3, but don't select a year, it will select the third week of all four years that I have in the dashboard. This is what I want.

That said, I think you're onto something with just using days * 7, I think thats probably cray cray enough to work.

Re: Current plus previous 13 weeks

richnorris — Thu, 24 Apr 2014 14:52:44 GMT

Sorry, was just being a nump-tron, it was applying the two set analysis' sequentially, with the second overwriting the first, so it was actually just doing Week <= Current, hence the extra weeks. what I actually needed was just to combine them into one big ol' statement like thus:

Sum({<Week ={">$(=num(Week(Today())-13))<=$(=num(Week(Today())))"}>} [$ VA])

Cheers for all your help though chaps, Jonas I reckon in the future I'll just do what you said, rather than messing around with the various different date-types. Seriously, why is there an AddMonths() but no AddDays / AddWeeks etc? So many caveats like that to deal with, better off just breaking everything down into days and then doing it the manly / manual way.

Re: Current plus previous 13 weeks

nagaiank — Thu, 24 Apr 2014 14:54:41 GMT

You may add a field WeekID in your calendar which has week number as sequence numbers.

Choose your dimensions as

If(WeekID >= (current WeekID -13), WeekID,Null())

You can store current WeekID in a variable or calculate using Max(WeekID)

Hope this helps.