topic Finding the diff of a column between current week and previous week in QlikView

Finding the diff of a column between current week and previous week

Sun, 17 Apr 2011 02:46:10 GMT

Hello,

Need some help in calculating the diff of a column between current week and previous week

Project, Version, Date, Person, Time
Sales, 1.1, 1/1/2011, John, 5
Sales, 1.1, 1/1/2011, Jim, 6
Sales, 1.1, 1/7/2011, Jim, 8
Sales, 1.2, 1/7/2011, John, 10
Sales, 1.2, 1/14/2011, James, 12
Sales, 1.2, 1/14/2011, Jim, 8

How can I calculate the difference in time between the selected date and the date before that?
for instance if 1/7/2011 is selected, then the time should be sum of all time with date 1/7/2011 - sum of all time with date prior to that, if there is no prior date then the same date should be treated as prior date, for instance 1/1/2011 with have the previous date as 1/1/2011.

when 1/1/2011 is selected diff should be 0
when 1/7/2011 is selected diff should be 7 (18-11)
when 1/14/2011 is selectd diff should be 2 (20-18)

when 1/14/2011 and 1/7/2011 is selected diff should be 7+2=9
when all the three dates are selected the diff should be 0+7+2 = 9
when 1/1/2011 and 1/14/2011 is selected the diff should be 0+2=2

Finding the diff of a column between current week and previous week

rwunderlich — Sun, 17 Apr 2011 05:18:16 GMT

You could assign the previous weeks sum to each group row in the script and then use a chart expression like:

=sum(SumTime - PreviousSumTime)

Your example above shows grouping only by date. Your attached qvw seems to indicate you want to group ny Project and Version. Not sure which you are after, but here's a script example by Date only. You can extend it if you need a finer grouping.

ByDate:
LOAD
Date,
sum(Time) as SumTime
RESIDENT data
GROUP BY Date
;

ByDate2:
LOAD
*,
Previous(SumTime) as PreviousSumTime
RESIDENT ByDate
ORDER by Date
;

DROP TABLE ByDate;

Finding the diff of a column between current week and previous week

Sun, 17 Apr 2011 14:49:29 GMT

Hello, Thank you for the reply.

If I sum up in the load script then when the diff for each individual at the pivot table is not correct.

So I modified the tables and added a table that would have previous date for each load date, and then used set analysis to calculate the diff.

However when more than one date is selected the value I get is incorrect. Please help.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

My sample file is attached.

Date selected - calculated diff

1/1/2011 - 0
1/7/2011 - 52
1/14/2011 - (-10)

But when all the three dates are selected, the diff should be 42 instead it shows as 148

This is because,
sum(106+158+148) - sum(106+158)
which is what my formula is.

But I would like to do

formula used : sum({$<Date=P(Date)>} Time)-sum({$<Date=P(PrevDate)>} Time)

sum(Time) - sum(prevTime) using set analysis for each selected date,
However date is not a dimension in the pivot table.