Skip to Content
0

Problems converting Date MM/DD/YYYY to trimester IDT

Nov 11, 2016 at 08:17 PM

145

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Tammy Powlas
Nov 12, 2016 at 09:53 AM
0

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.

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Tammy, and thanks for your answer. Are there some documentations to implement your suggestion?

Regards.

0
Juan Elías Rivero Domínguez

Use a local field - while formula would be different for you, concept is the same of what I am suggesting - see https://archive.sap.com/discussions/thread/3244066

1

Thanks for the suggestion Tammy. It's working very fine.

0
Sushil Padhye Nov 14, 2016 at 09:25 AM
0

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Sushill, thanks for your answer. The problem is that i need this dimension in the Universe. I tried your suggestions but is giving me error. I attached the screens.

2016-11-14-091634.png

2016-11-14-091727.png

What is wrong?

Regards.

0
Juan Elías Rivero Domínguez

remove the = (Equal to) operator from the formula and see.

0

Sorry, wrong print.

2016-11-14-111150.png

0
AMIT KUMAR
Nov 14, 2016 at 04:04 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Andrey Surinov
Nov 15, 2016 at 02:25 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded