Skip to Content
author's profile photo Former Member
Former Member

Conditional summation - If in Calculated column

Hi All,

I have a need to add values based on input month. The scenario is as below.

Month Value

01 V1

02 V2

03 V3

04 V4

05 V5

06 V6

07 V7

08 V8

09 V9

10 V10

11 V11

12 V12

Based on the month value I need to sum upto that month value. I have used the IF condition but the result does not seem to be right. Let me know whether this logic will do my need.

if('$$Month$$' = '01',V1,

if('$$Month$$' = '02', (V1+V2),

if('$$Month$$' = '03', (V1+V2+V3),

if('$$Month$$' = '04', (V1+V2+V3+V4),0

) -- 04

) --3

) --2

) --1

this is extended up to 12 months. But the result does not seem to be correct , any idea?

Thanks

Arthur.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Jul 07, 2016 at 06:28 PM

    Hi Arthur,

    You didn't give a very good explanation about your problem but I think I understand what you need. Based on the input parameter passed to the query you want the sum of the values from the beginning of the year up until the month selected.

    That can be done with query pruning with constant value (see a very well detailed explanation about that on Effective Query pruning using Constant Column in UNION node )

    In your case, I'm guessing you'll need one node for each month where you'd set the filters according to the desired values. I made a small example with 4 months only but that can surely be extended.

    So I got something like:

    Let us know if that's what you need.

    BRs,

    Lucas de Oliveira


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 07, 2016 at 05:59 PM

    Hi Arthur

    I was able to achieve what you want, let me put all the steps in a blog and pass you link .

    Regards

    Anindya

    Add a comment
    10|10000 characters needed characters exceeded

    • What is the total number of records in COSS and COSP ?

      If you have million ranges data and use some other variable/input parameter with it ( like Customer, Material , or GL_ACCOUNT ) , you still might get acceptable performance.

      Now 'acceptable' can mean many things .

      "Nested IF " is not working, because, at row number 1, you do not have value for row number 2. So, V1 is getting added to null ( for V2) and so on .

      Updated blog post with SQL approach and run time .

      Regards

      Anindya

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.