Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Jan 03 at 10:53 AM

    Hi - what is your data source?

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

    Seems ok.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 05 at 08:11 PM

    my data source is excel file with xlsx extension

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 11 at 02:05 PM

    Hi Tammy,

    Did you get chance to look into my reply?

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 22 at 04:21 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Ashutosh Rastogi 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

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

    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

    Add comment
    10|10000 characters needed characters exceeded