Skip to Content
author's profile photo Sreekanth Surampally
0
Sreekanth Surampally

HANA model - Count based on input parameter value

Posted on Aug 17, 2016 at 07:36 PM 150 Views

Hi, I have got a requirement to calculate the count based on input value from user, the required 2 columns are created and posted.

Order NumberCreated onPosting dateCreatedPosted40001201.12.201501.01.2016??40001401.12.201501.12.2015??40001501.06.201601.06.2016??

user selection is Year, there is no column which stores the year data.

It is going to be an input parameter/variable given to user for input, based on the selection value, created and posted columns are to be calculated.

the calculation is, if the given user value is 2015, "Created" would be number of orders which have created on date falls in 2015. similarly for "posted" as well.

In the above example data, if user input is 2015, then Created - 2 posted - 1.

I am thinking, what is the appropriate way to achieve this, should I be able to achieve it through 'Counter' or "new calculated column" and how?

Please let me know,

Thanks

Sreekanth

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Florian Pfeffer
    Florian Pfeffer
    Posted on Aug 17, 2016 at 08:20 PM
    1

    One option would be the following:

    • Create a projection node consuming the data from the table (or whatever your data source is).
    • Create a column engine filter expression like following (IP_YEAR is the input parameter, column names of your date columns have to be correctly set):
      string(component("CreatedOn",1)) = '$$IP_YEAR$$' or string(component("PostingDate",1)) = '$$IP_YEAR$$'
    • Create a calculated column "Created" with type Integer. The column gets a 1 if the year matches the input parameter, a 0 if it does not match. This allows a "counter aggregation". The column engine expression is like following:
      if(string(component("CreatedOn", 1)) = '$$IP_YEAR$$',1,0)
    • Create a second calculated column "Posted" with type Integer. I gets the same expression than the "Created" calculated column, of course refering to the "PostingDate" column.
    • Next create an aggregation node on top of the projection and add the calculated columns as aggregated columns to the output (with aggregation method "sum").

    Regards,

    Florian

    Add a comment
    10|10000 characters needed characters exceeded

    • Florian Pfeffer Sreekanth Surampally
      Aug 23, 2016 at 09:01 PM

      To consider also the previous year you only have to adjust the filter expression and the expression of the calculated column. In the following examples the "in" function is used to compare the column against the entered year value and the calculated previous year value.

      Filter expression:

      in(string(component("CreatedOn",1)) ,'$$IP_YEAR$$',string(int('$$IP_YEAR$$')-1)) or in(string(component("PostingDate",1)) ,'$$IP_YEAR$$',string(int('$$IP_YEAR$$')-1))

      Expression for calculated column (for instance for the CreatedOn column):

      if(in(string(component("CreatedOn", 1)) ,'$$IP_YEAR$$', string(int('$$IP_YEAR$$'))-1),1,0)

      Regards,

      Florian

    Comment on This Answer

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.