topic Re: Week Over Week Change in QlikView

Week Over Week Change

Wed, 03 Sep 2014 08:39:58 GMT

Hi ,

I need to show a table in my qlikview dashboard , where , the current weeks values are compared with last weeks value . E.g. Monday this week and monday last week. I need to show it in the format displayed beloow. I tried using reangesum , But it breaks as soon as i get Week Start Date dimesion in the chart. Can someone please help here ?

Week Start Date	Date	Current Week	Previous Week
8/3/2014	8/3/2014	10	9
8/3/2014	8/4/2014	20	12
8/3/2014	8/5/2014	30	14
8/3/2014	8/6/2014	11	15
8/3/2014	8/7/2014	23	6
8/3/2014	8/8/2014	34	4
8/3/2014	8/9/2014	32	0
8/10/2014	8/10/2014	35	10
8/10/2014	8/11/2014	39	20
8/10/2014	8/12/2014	42	30
8/10/2014	8/13/2014	45	11
8/10/2014	8/14/2014	48	23
8/10/2014	8/15/2014	51	34
8/10/2014	8/16/2014	54	32
8/17/2014	8/17/2014	57	35
8/17/2014	8/18/2014	61	39
8/17/2014	8/19/2014	64	42
8/17/2014	8/20/2014	67	45
8/17/2014	8/21/2014	70	48
8/17/2014	8/22/2014	73	51
8/17/2014	8/23/2014	76	54
8/24/2014	8/24/2014	35	57
8/24/2014	8/25/2014	39	61
8/24/2014	8/26/2014	42	64
8/24/2014	8/27/2014	45	67
8/24/2014	8/28/2014	35	70
8/24/2014	8/29/2014	39	73
8/24/2014	8/30/2014	42	76

Thanks,

Sailee

Re: Week Over Week Change

MK_QSL — Wed, 03 Sep 2014 08:41:49 GMT

Can you elaborate little more please?

Re: Week Over Week Change

Wed, 03 Sep 2014 09:17:35 GMT

HI Manish,

Basically , I want to show measure of current week days and last week days against current week dates ..Check the values in bold. In week of 8/3/2014, on first day value was 10. This value should appear as previous week value on first day of week of 8/10/2014.

Week Start Date	Date	Current Week Values	Previous Week Values
8/3/2014	8/3/2014	10	9
8/3/2014	8/4/2014	20	12
8/3/2014	8/5/2014	30	14
8/3/2014	8/6/2014	11	15
8/3/2014	8/7/2014	23	6
8/3/2014	8/8/2014	34	4
8/3/2014	8/9/2014	32	0
8/10/2014	8/10/2014	35	10
8/10/2014	8/11/2014	39	20
8/10/2014	8/12/2014	42	30
8/10/2014	8/13/2014	45	11
8/10/2014	8/14/2014	48	23
8/10/2014	8/15/2014	51	34
8/10/2014	8/16/2014	54	32

I hope this clarifies.

Thanks,
Sailee

Re: Week Over Week Change

Anonymous — Wed, 03 Sep 2014 09:28:49 GMT

Using Date as the dimension your first expression would simply be:

Sum(Amount)

And as long as you are loading the dates with numerical values (ie 8/3/14 = 41706 etc) then your second expression would be Sum({<Date={$(=Only(Date)-7}>}Amount)

Then you could compare by using Column(2) - Column(1) in a 3rd expression

Re: Week Over Week Change

Wed, 03 Sep 2014 09:41:06 GMT

Hi Adrian,

I tried the same. However , I am getting all 0 .

Re: Week Over Week Change

MK_QSL — Wed, 03 Sep 2014 09:41:10 GMT

Temp:

Load

Date(Date#([Week Start Date],'M/D/YYYY')) as [Week Start Date] ,

Date(Date#(Date,'M/D/YYYY')) as Date,

[Current Week],

[Previous Week],

Date(Date#(Date,'M/D/YYYY')-7) as PreviousWeekDate

Inline

[

Week Start Date, Date, Current Week, Previous Week

8/3/2014, 8/3/2014, 10, 9

8/3/2014, 8/4/2014, 20, 12

8/3/2014, 8/5/2014, 30, 14

8/3/2014, 8/6/2014, 11, 15

8/3/2014, 8/7/2014, 23, 6

8/3/2014, 8/8/2014, 34, 4

8/3/2014, 8/9/2014, 32, 0

8/10/2014, 8/10/2014, 35, 10

8/10/2014, 8/11/2014, 39, 20

8/10/2014, 8/12/2014, 42, 30

8/10/2014, 8/13/2014, 45, 11

8/10/2014, 8/14/2014, 48, 23

8/10/2014, 8/15/2014, 51, 34

8/10/2014, 8/16/2014, 54, 32

8/17/2014, 8/17/2014, 57, 35

8/17/2014, 8/18/2014, 61, 39

8/17/2014, 8/19/2014, 64, 42

8/17/2014, 8/20/2014, 67, 45

8/17/2014, 8/21/2014, 70, 48

8/17/2014, 8/22/2014, 73, 51

8/17/2014, 8/23/2014, 76, 54

8/24/2014, 8/24/2014, 35, 57

8/24/2014, 8/25/2014, 39, 61

8/24/2014, 8/26/2014, 42, 64

8/24/2014, 8/27/2014, 45, 67

8/24/2014, 8/28/2014, 35, 70

8/24/2014, 8/29/2014, 39, 73

8/24/2014, 8/30/2014, 42, 76

];

Join

Load

Date as PreviousWeekDate,

[Current Week] as PWEEK

Resident Temp;

PWEEK is your required field and compare it with [Previous Week]

Re: Week Over Week Change

Anonymous — Wed, 03 Sep 2014 09:45:28 GMT

Yes, of course that won't work because the set analysis is looking at the selection not dimension (sorry). I think the best solution would be to load the values offset by a week and call the field something like AmountPrevWeek.

Then your second expression would just be Sum(AmountPrevWeek)

Re: Week Over Week Change

Wed, 03 Sep 2014 09:56:08 GMT

Thanks Adrian and Manish. I was trying to avoid script level work .. But seems like it is the best option here