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

[former_member309335]

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

[Former Member]

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

[ashutosh_rastogi]

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

[former_member309335]

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?

[ashutosh_rastogi]

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

[former_member309335]

Hi Tammy,

Did you get chance to look into my reply?

[TammyPowlas]

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

[former_member309335]

my data source is excel file with xlsx extension

[TammyPowlas]

Hi - what is your data source?

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

Seems ok.