Skip to Content

I needed to do some working-days based calculation in a standalone HANA system and struggled quite a bit. Having achieved what I was aiming for, decided to share my solution. Hope it may help somebody in the future, too.

A pre-requisite for any of this is:

  1. l Table TFACS loaded/replicated from a SAP system
  2. l Your custom table DATA_TRANSFORM (explained below)
  3. l Generated Time-data in HANA (optional)

The essence of problem

Factory calendar in SAP is stored in a binary format which requires some creativity to consume. There is extra column per month in a year; each column contains 0-s and 1-s where each position means a day number within month and 0 means a day off and 1 means a working day.

Blog3 Picture1.jpg.png

Would be much more usable in calculations to have it like this:

Blog3 Picture2.jpg.png

After producing SQLScript Calculated view using formulas WORKDAYS_BETWEEN and ADD_WORKDAYS I still did not manage to consume this data in a graphical view, so the second attempt is below where I transpose months and days in two steps:

The solution XML is attached, so you may try plug and play, but a bit of logic explanation is here.

(1) Transpose months from columns to rows. I used a nice & simple idea brought in this blog for months: http://scn.sap.com/docs/DOC-51791 brought in by Abani Pattanayak and Martin Donadio.

Which in the case with months looks like this:

Blog3 Picture3.jpg

Where a projection of every month has a constant for the filed MONTH (from 01 to 12).

They are later put together in a union and output looks like this:

Blog3 Picture4.jpg.png

Better? ūüôā I think so. But we need to cut the field into pieces (calc. fields 001…031) and go on…

Now we could do the same transposing action again, this time with days (anybody wants 31 projections? ūüôā I don’t).

(2) Transpose days: I use Matrix transpose idea by Abani Pattanayak and Tony Cheung.

Step by step:

  1. Produce the matrix (or download from here: http://stuff.bi-consulting.eu/Blog3%20-%20Factory%20Calendar%20in%20HANA%20_worksheet_.xlsx) and load it to HANA as table DATA_TRANSFORM
  2. Add a calculated field with a nice tiny “if” statement, based on Matrix to fill the field DAYNUM:

if(“1”=1,’01’,

if(“2”=1,’02’,

if(“3”=1,’03’,

if(“4”=1,’04’,

if(“5”=1,’05’,

if(“5”=1,’05’,

if(“6”=1,’06’,

if(“7”=1,’07’,

if(“8”=1,’08’,

if(“9”=1,’09’,

if(“10”=1,’10’,

if(“11”=1,’11’,

if(“12”=1,’12’,

if(“13”=1,’13’,

if(“14”=1,’14’,

if(“15”=1,’15’,

if(“16”=1,’16’,

if(“17”=1,’17’,

if(“18”=1,’18’,

if(“19”=1,’19’,

if(“20”=1,’20’,

if(“21”=1,’21’,

if(“22”=1,’22’,

if(“23”=1,’23’,

if(“24”=1,’24’,

if(“25”=1,’25’,

if(“26”=1,’26’,

if(“27”=1,’27’,

if(“28”=1,’28’,

if(“29”=1,’29’,

if(“30”=1,’30’,

if(“31”=1,’31’,))))))))))))))))))))))))))))))))

  1. And one calculated field to calculate if we are dealing with a workday or not:

“001”*“1”+“002”*“2”+“003”*“3”+“004”*“4”+“005”*“5”+“006”*“6”+“007”*“7”+“008”*“8”+“009”*“9”+“010”*“10”+“011”*“11”+“012”*“12”+“013”*“13”+“014”*“14”+“015”*“15”+“016”*“16”+“017”*“17”+“018”*“18”+“019”*“19”+“020”*“20”+“021”*“21”+“022”*“22”+“023”*“23”+“024”*“24”+“025”*“25”+“026”*“26”+“027”*“27”+“028”*“28”+“029”*“29”+“030”*“30”+“031”*“31”

The end result can be polished by adding some Time Dimension data to get the YearMonth, date in SQL format, etc. Output looks like the screenshot in the beginning of a document.

Have fun!

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. abilash n

    Nice one Dmitry. Thanks for sharing. I am still thinking on any other options we have apart from this multiple step complex flow.

    (0) 
  2. Alexander Zlobin

    Hi! I solved this task wiht midstr formula .
    For example for string 0000110100111110011111001111100
    midstr(‘0000110100111110011111001111100, Day in Month number, 1)

    “Day In Month” – from M_TIME_DIMENSION table

    (2) 
  3. naresh jupally

    Hi Dmitry Kuznetsov,

    I have applied the logic which you have explained as above.

    I created Transpose of months in a Hana Calculation view and transpose of days in a table.

    How these 2 are inter linked with each other. I stuck here. i need further help to find out no. of days in a factory calendar in SAP HANA.

    Kindly help me the same as soon as possible.

    Awaiting your reply.

    Thanks in advance.

    Thanks and Regards,
    Naresh.

    (0) 
    1. Alexander Zlobin

      Hi , naresh jupally!
      I hope I can help u.  You have to do
      1 .  Projection of  Factory table TFACS with key YEAR
      1.a Calculated column FactoryString =  TFACS.MON01 + TFACS.MON02+ ….TFACS.MON12

      2. Agregation of M_TIME_DIMENSION table
      You have to create
      2.1 Columns YEAR and DATE_SAP
      2.2 Column DayNumInYear something ike¬†daysbetween(date(‚ÄúYEAR‚ÄĚ+‚Äô0101‚Ä≤),date(‚ÄúDATE_SAP‚ÄĚ)) + 1

      3. Join P1 with A2 (A2.YEAR = P1.YEAR)
      4. Create calculate column  FactoryFlag = midstr(FactoryString, DayNumInYear, 1)

      ps you can use M_TIME_DIMENSION-DAY  instead 2.2

      (1) 
  4. naresh jupally

    Hi Alexander Zlobin,

    I got No of Days but i facing one issue with these Results. Count of days are correct but the table TFACS does not have neither MANDT, RCLNT nor MADANT (System Client) Field which i require in mapping with other Hana Calculation views.

    I have created final Hana Calculation view and with union node i need to include No of days results. As there is no System Client i am unable to get correct results in my view.

    Now My requirement is to include System Client columns and that login client data should be populated dynamically.

    How can we achieve this?

    In BW we can achieve this using Formula variable with system field SY-MANDT. How about HANA? 

    Thanks and Regards,
    Naresh Kumar Jupally.

    (0) 
  5. naresh jupally

    Hi Dmitry Kuznetsov,

    Its correct but my requirement does not full fill with the output achieved.

    No of days is a small part of requirement.

    Initially we are creating Hana Calculation Views, then on top of View again Composite Provider then BEx Query.

    For my requirement i need to create a Union node in my calculation view and map respective attribute and measure in the union node.

    Data is arriving correctly but MANDT is missing for my No of Days. So in BEx i am unable get No of days count for a specific period. rest are correct. If i can able to manage MANDT in the output of my final Calculation view then my requirement can be closed.

    Kindly assist me with a solution thanks.

    Thanks and Regards,
    Naresh Kumar Jupally.

    (0) 
    1. Dmitry Kuznetsov Post author

      Well, since Factory Calendar is not SAP-Client-dependant, I see two options:
      1. If you are certain, that it is and will be a single-client system (including copy-back of Prod to QAS, etc.), you can use a constant
      2. if you want your solution bullet-proof, add an additional join to table T000 without joining on any field, you will have the same Factory Calender duplicated for every MANDT.

      Then you can use it further up your flows.

      Dmitry

      (0) 

Leave a Reply