Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Please help me on this.

6 Replies

Re: sum based on 2 variables

See the attached one

Not applicable

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

MVP
MVP

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)))

MVP
MVP

Re: sum based on 2 variables

Please find attached file for solution.

Regards,

Jagan.

Re: sum based on 2 variables

Hi Amuthabharathi,

Your answer is very helpful. And i need some clarfication.

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?

Community Browser