If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

cumulative totals for range of dates

Started by JonBoy1, 04 May 2011 10:18:17 AM

Previous topic - Next topic

JonBoy1

I've got a user who want's a report to return cumulative totals for a range of dates they select eg if they select 1st to 14th April 2011 they want the report to return 15 columns showing the cumulative results from the start of time upto and including each date. so column 1 would be the total from start of time to 1st April, the 2nd column cumulative total from start of time to 2nd of April and so on upto 15th April. Doing this for 1 date is easy but trying to do it for a selectable range of dates is proving difficult. Has anyone tried doing this? Any suggestions appreciated!

Thanks, jon

blom0344

I would define a range of measures like:

total(case when ?start_date? <= [fact_date] then [some_fact] else 0 end)

total(case when ?start_date? <= _add_days([fact_date],1) then [some_fact] else 0 end)

total(case when ?start_date? <= _add_days([fact_date],2) then [some_fact] else 0 end)

etc

where ?start_date? is the prompt parameter. Can't test it now, so you may need to perform some casting

Then simply add the calculations to a list object..