MTD ,YTD Calculations

[actcognosuser]

Hi All,
            How to calculate MTD ,YTD ,Timeline to Date(time betwn start and end o project) without displaying daily details.

I have a relational source.In croostab I pulled in current date.When I use the week(current_date) and do a aggregate .It gives me the same value as the current date instead of weekly count.same happens with MTD and timeline to date.

In the query i have a filter to pull the current date only.If i remove that and use current date as dataitem also..I get the same results.

Please advise.

[MFGF]

With a relational package, you will most likely need a separate query each for MTD, YTD and TLTD, with each query having a specific filter for the required time period.  These can then be joined and used in your main query.

Regards,

MF.

[actcognosuser]

Hi MFGF,
                Would I need to Union all these queries.I have 8-10 measures to report on.I would have to duplicate them 3 times,and  get the MTD,YTD and timeline values.could it be made simpler?

[MFGF]

I'd expect the queries to be joined rather that UNIONED.

[actcognosuser]

Do i need to create a dummy data item to join them?

[actcognosuser]

Hi MFGF,
                  I tried to join on a constant and also on country code.The count is a lot higher
than it is supposed to be.Cant get the union to work.Says set operation not compatible.
Check the order.Everything is right.Guessing there must be something to do with the aggregation properties since i have 10 measures.

[ksvchowdary]

[date] between _add_days(current_date,((_day_of_year((current_date)-1)*-1)) and current_date)

I Hhope it helps you.
Thanks ,
Sri Kalyanapu.

[mvjcognos]

mtd: date between first_of_month(date) and date
howevr u want current month from day 1 to to_date

if u want previous month 1st to last day then

previous mtd: date between first_of_month(add_months(date,-1)) and last_day(add_months(date,-1))