YTD Spend Total in report

[Revelator80]

I need to display on a report for a given business category both the amount of spend in a selected month and also the YTD spend up until that month (i.e. Business Services has spent £400k in January and in total £2.3m between April and January (financial year to selected date)). I am currently having a great deal of trouble doing this and wondered if anybody else had any advice.

The underlying data is in a relational format from a data warehouse. I have in place various date keys in indicate the year start date, month start and end date (data is aggregated to a monthly level) so believe I have the building blocks but am missing the knowledge to implement.

Many thanks

 

[blom0344]

This is usually a matter of creating 'buckets' through new calculations using CASE statement.

The 'bucket' for the selected month could be:
(?date selected? is parameter of the date prompt)

CASE WHEN
extract(year,?date selected?) = [factyear] and
extract(month,?date selected?) = [factmonth]
THEN [amount_spend]
ELSE (0)
END

assuming your fact hold both keys for month and year

The YTD will follow similar logic, depending on your data




Ties Blom

 

[Revelator80]

I've got that working now... New problem! I have prompts running from my time dimension for financial year and month (to get YTD and MTD figures). When I try to cascade from the year down to month, it sometimes greys out the month box and disables it (although this doesn't happen all the time, it does happen a lot of the time). If I don't cascade then I get repetition (e.g. April, April, April... for all the months). Any idea how I can get around this?

 

[blom0344]

In case of non-cascading prompts use seperate queries for each prompt. Cognos will automatically add a distinct clause to the query to fetch just the distinct values (which will limit the number of months to 12 in any case)

Ties Blom