Conditional summation - If in Calculated column

[Former Member]

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.

[lucas_oliveira]

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 )

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

[Former Member]

Thank you Lucas,

You are correct on one aspect, that my problem description is not very detailed. I have done the same thing you are proposing in another case by breaking the two columns in to 12 rows. But the issue I will run into is that the source table is a huge ECC controlling table. if I convert flat structure to multiple rows my table volume will multiply be 16. So I was looking not to do that, but achieve the same result with nested if...

The same is just for explanation only. I am talking about COSS and COEP table. where each column is for a period, and depending on the period I should sum up all values up to that period. In these tables no month value available as well. So it is bit challenging, but I have used a case within select to achieve the result, but there is still a challenge.

Even though the method you have given will work it is making the model bit too large and too many nodes. I want it to be achieved thru nested if, if I can as that is simple. I will keep the post open for now.

I do appreciate your effort to help me.

Thank you,

Arthur.

[lucas_oliveira]

Hi Arthur,

I'm thinking '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

[anindya_bose]

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

[Former Member]

Anindya,

Thank you. It may be helpful for others as well.

Arthur.

[anindya_bose]

Here you go

[Former Member]

Anindya,

Thank you so much for your effort.It will help, but still this method will make the records go up tremulously as the base table I am working on is COSS and COEP. It will multiply the total no of records by 16 at the least. I have replied to Lucas as he is suggesting the same.

My question is why nested if statement is producing wrong result? I would want to get it fixed with out increasing the row count.

Hope you understand .

Arthur.

[anindya_bose]

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