Hello All,
In my report i have a requirement to calculate cumulative sum.
Exampl:
My table has one month data, from 1st to 30th.and in my report i have to calculate week_to_date and Month_to_date. Something like partition by clause in Oracle. Since i am using access database i am unable to do it in backend. Please help/guide me to achive this.

Thanks In advance

This message has been edited. Last edited by: Kerry,

Hi,

Lot of ways you could do this...
You can use Multiverb request with 2 SUMs at 2 diff BY levels..
COMPUTE/DEFINE fields for summing at WTD or MTD level..
It all depends on the data in your source..if the data is at day level ...you could SET amper variables for WTD and MTD date components and use them in DEFINE or use them in WHERE if you go the multiverb way.

  
DEFINE FILE FILENAME
WTD/D12.2 = IF SOURCE_DATE FROM '&WEEK_BEGIN' TO '&WTD' THEN AMOUNT ELSE 0;
MTD/D12.2 = IF SOURCE_DATE FROM '&MONTH_BEGIN' TO '&MTD' THEN AMOUNT ELSE 0;
END
TABLE FILE FILENAME
SUM
WTD
MTD
END

thanks
Sashanka

Thanks for the reply Sashanka.

What i was looking for is,I should have a final focus file which has all the dates from 1 to 30th and Week_to_date and Month_to_date calculated with respect to each date. so, if apply a filter on 15th the WTD,MTD should be calculated w.r.t 15th..

Thanks For the help

Renji,

Try something like this:

TABLE FILE whatever
SUM AMOUNT
    COMPUTE WTD/D12 = IF FST.WEEK EQ LAST FST.WEEK THEN WTD + AMOUNT ELSE AMOUNT;
    COMPUTE MTD/D12 = IF FST.MONTH EQ LAST FST.MONTH THEN MTD + AMOUNT ELSE AMOUNT;
BY  DATE
BY  WEEK NOPRINT
BY  MONTH NOPRINT
END

Greets, Dave

quote:

Originally posted by Dave:
Renji,

Try something like this:

TABLE FILE whatever
SUM AMOUNT
    COMPUTE WTD/D12 = IF FST.WEEK EQ LAST FST.WEEK THEN WTD + AMOUNT ELSE AMOUNT;
    COMPUTE MTD/D12 = IF FST.MONTH EQ LAST FST.MONTH THEN MTD + AMOUNT ELSE AMOUNT;
BY  DATE
BY  WEEK NOPRINT
BY  MONTH NOPRINT
END

Greets, Dave

Shouldn't that be like below?:

    COMPUTE WTD/D12 = IF FST.WEEK EQ LAST FST.WEEK THEN LAST WTD + AMOUNT ELSE AMOUNT;
    COMPUTE MTD/D12 = IF FST.MONTH EQ LAST FST.MONTH THEN LAST MTD + AMOUNT ELSE AMOUNT;

Hi, Wep5622.

Yes, that's what you'd expect.

But for some reason WF doesn't mind.
I guess because it's not a real column. Just some local variable the engine uses to create the compute. Refering to it seem to simply return whatever it is at that moment.

TABLE FILE CAR
	SUM	SALES
		COMPUTE CUM_SALES/D12 = CUM_SALES + SALES;
		COMPUTE CUM_SALES_2/D12 = LAST CUM_SALES_2 + SALES;
	BY	CAR
END

Groeten, Dave
Are you going to BI-Symposium next week?

Thanks all for the help ...