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.

10|10000 characters needed characters exceeded

### Related questions

• 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

10|10000 characters needed characters exceeded
• Lucas Oliveira Former Member

Hi Arthur,

I'm thinking @Anindya Bose 's suggestion could be more thorough in your case.

Anyhow, provide more details about the scenario so we can see how it fits there or on another solution.

BRs,

Lucas de Oliveira

• 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

10|10000 characters needed characters exceeded
• Anindya Bose Former Member

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