How to show transaction amount in two columns, one for yesterday, one for MTD

[sean]

Hi everyone,

I am using cognos 10.2. I tried to insert 2 columns in a sales report by department, one column for sales amount for yesterday and the other for month to date. Is this achievable?

This is a query built on GL Transaction. I can build 2 lists and place filters base on Trans Date but I would like to combine them if possible. The column will look like this:
Department | Sales Yesterday | Sales Month to day

Thanks,
Sean

[BigChris]

Create two calculations:

total(if([Date] = cast(_add_days(getdate(),-1),date)) then ([Amount]) else (0))

and

total(if([Date] between cast(_first_of_month(getdate()),date) and cast(_add_days(getdate(),-1),date)) then ([Amount]) else (0))

[sean]

Thanks heaps BigChris :-).