topic everyday sum of past 5 days in QlikView

everyday sum of past 5 days

Wed, 05 May 2010 13:45:58 GMT

I'm trying to make a sum for the past 5 days every day:

example: if the date is 07-05-2010 I want the sum of 06-05-2010, 05-05-2010, 04-05-2010, 03-05-2010, 02-05-2010

for 06-05-2010 it will be 05-05-2010, 04-05-2010, 03-05-2010, 02-05-2010, 01-05-2010 etc...

I have tried several things including the expression below but nothing seems to work...

sum({$<DATE = {">=$(=date(DATE-6,'DD/MM/YY')) <=$(=date(DATE-1,'DD/MM/YY'))"}>} Close)

everyday sum of past 5 days

Wed, 05 May 2010 14:30:49 GMT

Date comparison in set expression seem to work only with strings.

Do you have date settings as DD-MM-YYYY or DD/MM/YY?

Verify this and try to see whether it works when you provide the date in string format.

">='01/05/10' <='05/05/10'"

Re. :everyday sum of past 5 days

martin59 — Wed, 05 May 2010 14:34:14 GMT

Hi William,

How do you selection your date ? It's the current date or a selection in a field ?

If it's based on the current date :

sum({$<DATE = {">=$(=(Today()-6)) <$(=Today())"}>} Close)

If it's based on a selection :

sum({1<DATE = {">=$(=(Only(DATE)-6)) <$(=Only(DATE))"}>} Close)

everyday sum of past 5 days

Wed, 05 May 2010 16:41:45 GMT

tried that already... no effect

everyday sum of past 5 days

Wed, 05 May 2010 16:47:17 GMT

to clear things up:

DATE SALES TODAY (sum)SALES LAST 5 DAYS

31-1-2010 10 130

30-1-2010 20 170

29-1-2010 30 etc

28-1-2010 20 etc

27-1-2010 10 ......

26-1-2010 50

25-1-2010 60

everyday sum of past 5 days

Wed, 05 May 2010 18:27:37 GMT

What about this one

=sum({<SALES_DATE={">=$(=num(getFieldSelections(SALES_DATE)-4))"}>} total AMOUNT)

everyday sum of past 5 days

johnw — Wed, 05 May 2010 18:38:21 GMT

Set analysis can't solve the problem because the set is only evaluated once for the chart instead of once per ROW of the chart.

Here's a chart solution:

rangesum(below(sum(Close),1,5))

I tend to prefer data solutions to chart solutions, as simply reversing the sort on the Date will mess up a chart with the above expression. Skipped dates due to selections may cause similar problems. In this case, I would consider making a separate table with data like this:

AsOfDate, DateType, Date
31-1-2010, Today, 31-1-2010
31-1-2010, Last5, 30-1-2010
31-1-2010, Last5, 29-1-2010
31-1-2010, Last5, 28-1-2010
31-1-2010, Last5, 27-1-2010
31-1-2010, Last5, 26-1-2010

Which can be done (a bit inefficiently) like this, assuming you already have a Calendar table:

[Date Linkage]:
LOAD Date as AsOfDate
FROM [Calendar]
;
LEFT JOIN ([Date Linkage])
LOAD
Date
,'Last5' as DateType
FROM [Calendar]
;
INNER JOIN ([Date Linkage])
LOAD *
FROM [Date Linkage]
WHERE Date < AsOfDate
AND Date >= AsOfDate - 5
;
CONCATENATE ([Date Linkage])
LOAD
Date as AsOfDate
,'Today' as DateType
,Date as Date
RESIDENT [Calendar]
;

Then build your chart like this:

Dimension = AsOfDate
Expression1 = sum({<DateType={'Today'}>} Close)
Expression2 = sum({<DateType={'Last5'}>} Close)

everyday sum of past 5 days

Wed, 05 May 2010 20:39:26 GMT


John Witherspoon wrote:
Set analysis can't solve the problem because the set is only evaluated once for the chart instead of once per ROW of the chart. <div></div>

Thanks John, clear for me now.. I'll go for the data solution :-))

everyday sum of past 5 days

Fri, 13 Jan 2012 17:53:54 GMT

Hi John,

I found this blog, it's somewhat similart to the other discussion I created. I've implemented the below script. But I'm getting the same values for each different flag. What am I missing?

[DateLinkage]:
LOAD Date as AsOfDate,
Month as AsOfMonth,
Year AS AsOfYear
Resident Calendar
;
LEFT JOIN ([Date Linkage])
Load Date
Resident Calendar;

INNER JOIN ([Date Linkage])
LOAD *,if(Date < AsOfDate AND Date >= AsOfDate - 28,1) AS RollingOneMonthFlag
Resident [Date Linkage];
INNER JOIN ([Date Linkage])
LOAD *,if(Date < AsOfDate AND Date >= AsOfDate - 56,1) AS RollingTwoMonthsFlag
Resident [Date Linkage];
INNER JOIN ([Date Linkage])
LOAD *,if(Date < AsOfDate AND Date >= AsOfDate - 74,1) AS RollingThreeMonthsFlag
Resident [Date Linkage];

CONCATENATE ([Date Linkage])
LOAD
Date as AsOfDate,
Month As AsOfMonth,
Year AS AsOfYear,
'Today' as DateType
,Date as Date

Resident Calendar

everyday sum of past 5 days

Mon, 16 Jan 2012 19:28:53 GMT

Anyone else out that can help me? Still pounding away at this.....