topic Re: Negative Weeks in QlikView

Negative Weeks

Anonymous — Mon, 31 Mar 2014 19:07:58 GMT

Hello,

I was wondering how I go about subtracting weeks so that I do not result in a negative week. For example, if it is currently week 12 and I am subtracting 13 weeks, I do not get a value.

I have tried to determine the POS Net Sales for 13 weeks from the Current week using Set Analysis:

SUM({$<Year={$(=Max(Year))},

Week=($(=(vCurrentWeek)-13))},

SKU=P(SKU)>} [POS Net Units])

However, I need to somehow incorporate the following if statement:

	if($(=(vCurrentWeek)-13)<0, Week=$(=(vCurrentWeek)-13)+52)

Any help would be greatly appreciated! I am trying to make it uniform so that if I subtract say 14, or 15 weeks, it will also work.

Re: Negative Weeks

Gysbert_Wassenaar — Mon, 31 Mar 2014 19:22:27 GMT

The usual solution is to create a serial week field in the script and use that field instead. By serial week field I mean a field with values where every weeks value is 1 larger than the previous weeks value. Something like autonumber(Year*100+Week,'_WeekCounter') as _WeekSerial. If you have a date field you can use the Qlikview Components script library to create a Calendar table from that date field and you'll get the _WeekSerial field in that table too.

Re: Negative Weeks

Mon, 07 Apr 2014 12:49:41 GMT

Hi Isabel

You could try using the mod(int1,int2) function in your formulae. It returns the modulus of two integers, ie the remainder when you divide int2 by int1. It also works for negative numbers:

mod(-2,52)

returns "50"

Erica