6 Replies Latest reply: Sep 30, 2013 7:29 AM by Settu Periyasamy

# set analysis based on sumif condition with 2 criteria

Dear All,

Could you please convert the below " if " expression into sum expression. Let me explain the problem.

I have 4 dimension and 2 sales value. I need to display the dimension (flexible) if the sales is greater than  0(zero).

=if(sum({\$<\$(vComparison_Selection)>}VBRK.\$(vCurrency)KZWI2)>0 and

Sum(VBRK.\$(vCurrency)KZWI2)>0,

Sum(VBRK.\$(vCurrency)KZWI2))

see the below example.(base data)

dim1     dim2     val(expression)     val1

a          a1          100                    0

a          a2          200                    500

a          a3          0                        25

a          a4          50                      75

b          b1          55                      0

b          b2          0                        55

b          b3          25                      100

Result should be

dim1     dim2          val          val1

Total           275          675

a          a2               200          500

a          a4                 50            75

b          b3                 25          100

• ###### Re: sum based on 2 variables

See the attached one

• ###### Re: sum based on 2 variables

Hi,

Use the following two expression

val(expression)=IF([val(expression)]>0 AND val1>0,[val(expression)])

val1=IF([val(expression)]>0 AND val1>0,val1)

PFA

• ###### Re: sum based on 2 variables

Hi Amuthabharathi,

I have to use the 2 variables in a single field. like comparision selection (one variable), currency (another variable) and the field name is Sales compartion_selection(VBRK.EURKZWI2)

EUR - vCurrency

comparision_selection = vComparision_Selection

VBRK.KZWI2 - Sales value

for e.g

(vComparison_Selection)

VBRK.\$(vCurrency)KZWI2

Now, I need to display the dimension( various dimension ), if the sales value is greater than Zero

how to apply the proper format?

• ###### Re: sum based on 2 variables

May be like attached ?

• ###### Re: sum based on 2 variables

You cannot do it in sum expression

It can be something like

=If(RangeMin(Sum({\$<\$(vComparison_Selection)>}VBRK.\$(vCurrency)KZWI2), Sum(VBRK.\$(vCurrency)KZWI2)) > 0,

Sum(VBRK.\$(vCurrency)KZWI2)))

• ###### Re: sum based on 2 variables

Please find attached file for solution.

Regards,

Jagan.