topic Re: sum based on 2 variables in QlikView

set analysis based on sumif condition with 2 criteria

settu_periasamy — Thu, 26 Sep 2013 06:52:08 GMT

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.

Re: sum based on 2 variables

Kushal_Chawda — Thu, 26 Sep 2013 07:28:17 GMT

See the attached one

Re: sum based on 2 variables

Thu, 26 Sep 2013 07:36:12 GMT

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

tresB — Thu, 26 Sep 2013 07:41:11 GMT

May be like attached ?

Re: sum based on 2 variables

CELAMBARASAN — Thu, 26 Sep 2013 07:41:36 GMT

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

jagan — Thu, 26 Sep 2013 07:53:04 GMT

Please find attached file for solution.

Regards,

Jagan.

Re: sum based on 2 variables

settu_periasamy — Mon, 30 Sep 2013 07:38:01 GMT

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?