topic Re: Week vs. Previous Week in QlikView

Week vs. Previous Week

danielle_v — Tue, 26 Feb 2013 12:04:54 GMT

Hi All,

I'm putting together a report where I need to look at the selected week/year and be able to compare this to the previous week, the week before that etc.

At the moment, I have 2 calendar objects - one for year and one for week. The user will select the year and the week in order to view the results.

This works fine, apart from over the start of a new year. If I select week 1 2013 for example, I would want the previous week expression to show week 52 2012. However thsi does not seem to work.

I have tried creating a week-year filed in my script, but can';t seem to get anything to work.

Any help greatly appreciated!

Re: Week vs. Previous Week

Gysbert_Wassenaar — Tue, 26 Feb 2013 12:09:12 GMT

You can create a serial week field with this:

AutoNumber(weekyear(Date) &'|' & week(Date) as WeekSerial

Re: Week vs. Previous Week

azimabadi — Tue, 26 Feb 2013 12:12:27 GMT

can you please post set analysis for previous week?

Re: Week vs. Previous Week

danielle_v — Tue, 26 Feb 2013 13:15:10 GMT

Hi Gysbert - I have just tried this and I get numbers rather that week/years - for example week 10 2013 is showing in my list box as 394. Is it possible to show these as week/years in the list box?

Hi Peyman - The set analysis will be something like

ceil(sum({$<Cal_WeekYear = {Cal_WeekYear - 1}>} SalesValue))

So if I selected week 10 2013 in my calendar, I would want the above expression to show the sum of SalesValue for week 9 2013. (if I had selected week 1 2013 in my calendar, I would want the expression for previous week to show the sum of SalesValue for week 52 2012).

Thanks!

Re: Week vs. Previous Week

Gysbert_Wassenaar — Tue, 26 Feb 2013 13:35:27 GMT

Danielle,

The idea is to use the serial weeknumber field in the set analysis expressions and week and year fields for making the selections. sum({<Cal_WeekYear=, Cal_Year=, WeekSerial={$(=max(WeekSerial)-1)}>} SalesValue) will return the right result when you select week 1 in 2013, i.e. the sales of the last week of 2012.

Re: Week vs. Previous Week

azimabadi — Tue, 26 Feb 2013 15:10:46 GMT

how looks Cal_WeekYear like? is it something like "201301" or "2013-01" for first week of 2013?

Re: Week vs. Previous Week

danielle_v — Tue, 26 Feb 2013 15:13:53 GMT

This works perfectly! Many thanks Gysbert!