Skip to Content
0

SAP Lumira 2.1 Discovery doesn't create Date hierarchy values correctly?

Jan 03 at 09:35 AM

313

avatar image

I recently installed SAP Lumira 2.1 Discovery and have Order Date as column and it has date in YYYY-MM-DD format.

1) When I create "Date Time" objects on Order Date, it creates 4 new dimension objects with Order Date_Day, _Month, _Quarter, _Year and all values look correct

2) I also tried creating custom hierarchies using newly created objects Order Date_Year, Order Date_Quarter, Order Date_Month but they are not shown in the list of objects to create a hierarchy on my own. This is so strange??

3) When I create Order Date -> Hierarchies -> Date/Time, a new hierarchy is created with name Time_Order Date and it has 4 objects : Year, Quarter, Month, Day but Month and Day objects have incorrect values in format: YYYY/Q1/1. They are showing Quarter values instead of Month / Day. Year and Quarter objects show right values.

Attached screenshots. Hierachy Created as below

Hierachy Values are as below

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

5 Answers

Tammy Powlas
Jan 03 at 10:53 AM
0

Hi - what is your data source?

Here's what I get with 2.1 Discovery using Excel as a data source:

Seems ok.


2dot1dates.jpg (107.1 kB)
Share
10 |10000 characters needed characters left characters exceeded
Santhosh Kumar Alladi Jan 05 at 08:11 PM
0

my data source is excel file with xlsx extension

Share
10 |10000 characters needed characters left characters exceeded
Santhosh Kumar Alladi Jan 11 at 02:05 PM
0

Hi Tammy,

Did you get chance to look into my reply?

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

I am not getting notified on answers; did you try the time hierarchy?

0
Ashutosh Rastogi
Feb 22 at 04:21 AM
0

Hi Santosh,

My comments w.r.t your points

  1. I understand the behavior is as expected, so no issues.
  2. Lumira provides you two options, either to split and pickup pieces of date or create a hierarchy straightaway. Now, in case if you choose to split and then later use the individual dimensions to create a new hierarchy, then system would not be able to uniquely identify Month (say Jan) to its right year (say 2011, 2012, 2013). However, in case if you directly create hierarchy, the values retain their parent info as well. hence, two approaches address two different usecases.
  3. Month is displayed in numbers in this case. "2011/Q1/1" represents January, Q1, 2011. Whereas "2011/Q1/3/1" represents 1st March, Q1, 2011.

Hope that clarifies.

Regards,

Ashutosh

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

Hi Ashutosh,

1. No issues

2. Still ok

3. How would it makes sense to show "2011/Q1/1" in Month column when my date value is one of the date in January. I never need quarter (Q1) information in Month column at all. How do I get only Month information?

0
Santhosh Kumar Alladi

Hi Santhosh,

If you just need month (not even year) then you can choose to split the date without creating a hierarchy.

However, if you need month along with year but no quarter, there is no straight forward way. A workaround could be to use following formula to create a derived dimension

if (SubString({Month}, 9) = "1") then Concatenate("Jan ", SubString({Month}, 1, 4)) else if (SubString({Month}, 9) = "2") then Concatenate("Feb ", SubString({Month}, 1, 4)) else if (SubString({Month}, 9) = "3") then Concatenate("Mar ", SubString({Month}, 1, 4)) else if (SubString({Month}, 9) = "4") then Concatenate("Apr ", SubString({Month}, 1, 4)) else if (SubString({Month}, 9) = "5") then Concatenate("May ", SubString({Month}, 1, 4)) else if (SubString({Month}, 9) = "6") then Concatenate("Jun ", SubString({Month}, 1, 4)) else if (SubString({Month}, 9) = "7") then Concatenate("Jul ", SubString({Month}, 1, 4)) else if (SubString({Month}, 9) = "8") then Concatenate("Aug ", SubString({Month}, 1, 4)) else if (SubString({Month}, 9) = "9") then Concatenate("Sep ", SubString({Month}, 1, 4)) else if (SubString({Month}, 9) = "10") then Concatenate("Oct ", SubString({Month}, 1, 4)) else if (SubString({Month}, 9) = "11") then Concatenate("Nov ", SubString({Month}, 1, 4)) else Concatenate("Dec ", SubString({Month}, 1, 4))

Regards,

Ashutosh

0
avatar image
Former Member Apr 20 at 07:05 AM
0

Hello Tammy,

why do you get the "old" style of the hierarchical elements? I just get it like Santhosh, but I need it in the same way like you got it.

I get for day: 2018/Q1/2/22

I want for day: 22

Also I have a problem with sorting the values numeric, Lumira is now sorting alphabetic.

I'm using Lumira Discovery 2.1 SP01.

Greetings,

Sascha

Share
10 |10000 characters needed characters left characters exceeded