Object in the universe to determine YTD, MTD or daily given a prompted date

[Former Member]

Hi,

I would like to create an object in my universe to determine if I need the figure for YTD calclations, MTD calculations or Daily calculations.

I have got a table DAYS with a column DAY (date type).

The variable would return 'Daily' if the DAYS.DAY is the prompted day. It would return 'MTD' for every DAYS.DAY with the same month as the prompted day and it would return YTD for every else day.

I have tried with CASE clause but I get an error "invalid number"

CASE

WHEN @Prompt('Enter values for Production Day:','D',,Mono,Free,Not_Persistent,,User:0) = DAYS.DAY THEN 'Daily'

WHEN TRUNC(@Prompt('Enter values for Production Day:','D',,Mono,Free,Not_Persistent,,User:0),'MONTH') = TRUNC(DAYS.DAY,'MONTH')  THEN 'MTD'

ELSE 'YTD'

END

Is this a good approach? How can I solve this with an object in the universe?

Thanks,

Jonás

[mhmohammed]

Hi Jonas,

How about trying this approach from 's blog? You'll have to tweak it a little bit with the table.columns you're using and also the Daily or MTD or YTD text you want to see.

Let us know.

Thanks,

Mahboob Mohammed