Dateadd and Datediff equivalent in Universe IDT

[Former Member]

Hey experts

I am trying to create an indicator field in my Universe where based on a certain criteria for my date in the table, the indicator has some values.

My indicator  should say 'Y' if my date is in the last 12 months, 'P' if between 13 and 24 months and N otherwise.

Here is the screenshot of the sql formula I am using which works fine...

I have attached the screenshot because I cannot copy and paste in SCN forum ... don't know why!!

I have tried with 'timestampadd' function but cant seem to find the first day and last day of month equivalent ...

Here 

any ideas?

cheers

[Former Member · Accepted solution]

That's right. My universe was a multi source and it does not handle sql functions very well. I have now switched it back to the single source and its all good now.

[Former Member]

IDT is the semantic layer which maps the business language to technical language. As long as you use Single Source Connection, you can use every (almost) expression that are valid in the underlying relational database.

[mhmohammed]

Hi Buddy,

Please create an Object in the Universe, copy and paste the case statement you've in the image (starting case......to..... end) in the Select clause of the object and try to parse it. That should work fine as long as the case statement you've is correct, the date column name is call_date, table name is table123 (or whatever), and you have the table123 added in the data foundation layer.

You might want to update the case statement as

case

     when call_date between ___ and ___ then 'last 12 months'

     when call_date between ___ and ___ then 'last 12 to 23 months'

     .

     .

     .

     else 'older than x months'

end

Thanks,

Mahboob Mohammed