Previous 13 months data in HANA studio

[haripriya_pampana]

We have the below requirement to calculate the previous 13 Months?

For example if we provide  december 2015(month) as a variable (Single input variable) client would like to view the data from December 2014 to december 2015 as 13 months data

How can I calculate the same under Calculation view.

how can i write expression for single input variable.

for work around i have created range variable but client is asking for single input variable for month and it retrieves 13 months data.

Thank you

[anindya_bose · Accepted solution]

Hi

Check this thread , you need to make a small change in the Filter Expression.

For month it would be little difficult though .  Here how I achieve this.

My model is like Projection 1 ------------> Projection 2 ------> Aggregation ------>Semantic

First I created an Input Parameter of type NVARCHAR(6)

Step 1 :

In Projection 1, I created a Calculated Column which would be calculated as "Month Input By User   - 13 "  .  Definition of Calculated Column is like below

Step 2:.

In Projection 2, I used Calculated Column and Input Parameter both to filter on my Calmonth .

Here CALMONTH = Month in my base table  in YYYYMM format .   USER_MONTH =  Name of my Input Parameter

Here is the piece of code, you might need slight adjustment.

Projection 1 Calculated Attribute : 

if     ( (  rightstr  ('$$USER_MONTH$$',2) = '01' ) , string( int (leftstr ('$$USER_MONTH$$',4)) - 2 ) + '12'    ,

  string( int (leftstr ('$$USER_MONTH$$',4)) - 1 ) + lpad (  ( string( int(rightstr ('$$USER_MONTH$$',2)) -1 )  ) ,2,'0')

  )

Projection 2  Filter Expression : "CALMONTH" >= "MONTH_MINUS_13" AND "CALMONTH" <='$$USER_MONTH$$'

Should you need explanation of the logic in Calculated Column, let me know.

Cheers

Anindya

[anindya_bose]

Did you try it ?  Did it work for you ?  Thanks for sharing the update.

Cheers

Anindya

[haripriya_pampana]

its not working anindya....

can you please give any idea

[anindya_bose]

it should work, I tried this in my system and created those formula for calculated attribute.

I would suggest to create two calculated attributes and see how the data is coming there based on USER INPUT.

Like if USER INPUT is 01/2016  then two column should show  01/2016 and 12/2014 . Once you achieve this, then only use these two cal columns to filter data.

What problem you are facing here ?

Cheers

Anindya

[rindia]

What is your data type and length of variable?

[haripriya_pampana]

Anindya,

i will try for the above solution and update you

Thank you

[anindya_bose]

Make sure your User Input is in YYYYMM format.  If not, slight change would be required in the script.

One more thing, I put the code for 13 months.  I.e If Input is 01/2015 , then data would be from 12/2013 - 01/2015   ( last 13 months and current month ) .  But I think your requirement is easier than I thought .  Put the following calculation under Calculated Attribute definition. Rest would be same.

Projection 1 Calculated Attribute :

   str( int ( leftstr ('$$USER_MONTH$$',4 ) ) - 1 )  + rightstr(('$$USER_MONTH$$',2)

Let's say your input is 201501 , then this formula would take the first four character i.e 2015 , convert it to integer and then subtract 1 from it. So , this first part would give 2014 .

In the second part, we would take last two character , i.e 01 and concatenate with first part.

In the end , we would get concatenation ( 2014,01 ) ..i.e 201401 .

In second projection use the filter as suggested.

***Last formula I did not write in actual hana studio, so little modification might be required.

Anindya

[anindya_bose]

Hi Pampana

Sorry for late reply.

This can be better achieved by using Input Parameter type "Procedure/Scalar function "

Have look at this blog

Best Regards

Anindya