everyday sum of past 5 days

[#1]

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)

[#2]

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'"

[martin59]

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)

[#4]

tried that already... no effect

[#5]

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

[#6]

What about this one

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

[johnw · Accepted solution]

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)

[#8]

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 :-))

[#9]

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

[#10]

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