Skip to Content

Problems converting Date MM/DD/YYYY to trimester IDT

Hi Experts,

I need create a dimension date type in IDT(information design tool) my source is SAP ERP query.

From the Date Dimension object, i am getting the following format as MM/DD/YYYY, and i need to show the date on trimester (Q1,Q2,Q3,Q4). I'm trying create a dimension that crops the original date and shows only MM and after, i will use a condition that uses the months and convert it to trimester. I read something of cast and convert functions but i don't understand how can handle the functions.

How can achieve that? Please explain to me step to step.

Using ERP and IDT 4.2

Regards.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Nov 12, 2016 at 09:53 AM

    I would create those quarters back in the InfoSet or the SAP Query - it will be faster for your universe if those calculations are done back in ECC.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 14, 2016 at 09:25 AM

    Hello,

    If you want to show trimester at Report Level you can directly use =Concatenation("Q";Quarter(<date dimension>)).

    In IDT

    =Concat("Q";datepart(qq,<date dimension>))

    This will give you trimester in format Q1,Q2,Q3,Q4 based on the date value.

    Regards,

    Sushil Padhye

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 14, 2016 at 04:04 PM

    You don't have Datepart function in IDT with SAP infoset..Datepart is database specific function.

    try this if you date object is date datatype.

    quarter(@catalog('dummy')."PUBLIC"."dummy1"."DATE")

    you will 1,2,3,4 values in this.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 15, 2016 at 02:25 PM

    Is your universe multi-source enabled or a single source one?

    Even although you're using a single query you might want to create your universe as multi-source enabled to be able to use SQL-92 syntax and access a broader variety of date-time and string conversion functions.

    There is of course a drawback: all queries would go through an additional BIP service, so called Data Federator, which might cause performance issues as well as some oparation issues.

    Good luck

    Add comment
    10|10000 characters needed characters exceeded