Calculating the MTD Sales In A Report Page

[dfcrj]

I tried searching for an answer before I posted but no luck.
I'm extremely new to writing reports but I've been asked to add this into a customer report where the value is displayed whenever they look at the customer details.

I'm trying to determine the MTD sales but I don't know how.
I know how to right it in SQL but I'm not working there anymore so I'm trying to learn cognos.

(Month(TransactionDate) = Month(GetDate()) and Year(TransactionDate) = Year(DateAdd(yy-1, GetDate())

any help would much appreciated on this issue or any advice.
thanks again

 

[blom0344]

CASE WHEN
(extract(Month,TransactionDate) = extract(Month,current_date) and (extract(Year,TransactionDate) = extract(Year,current_date))
Then ([Somemeasure]) else (0) End

Set aggregation to total for this query calculation.

If you only want to fetch MTD as measure, then you can define a filter along similar lines:

extract(Month,TransactionDate) = extract(Month,current_date) and (extract(Year,TransactionDate) = extract(Year,current_date)

Ties Blom

 

[dfcrj]

thank you!