topic Re: Using two conditions in set analysis in QlikView

Using two conditions in set analysis

Husky_Bab12 — Thu, 18 Aug 2022 16:42:27 GMT

Hi,

I'm trying to implement two conditions in set analysis as below,

=sum({<[Arrived Run Total]={">=0"} , [RunTot_Quantity]={"<0"}>} Quantity).

I need to sum all the quantities when the corresponding [Arrived_run Total] >=0 and [RunTot_Quantity]>0.

I'm trying to do it but unable to achieve the expected result.

I'm just able to add the quantities which has [Arrived Run Total] >=0 leaving out quantites which has [RunTot_quantity]>0.

Can someone help here?

Re: Using two conditions in set analysis

luizcdepaula — Thu, 18 Aug 2022 17:15:27 GMT

Hi,

Try the below:

=sum({<[Arrived Run Total]={"= Sum([Arrived Run Total])> 0"} , [RunTot_Quantity]={"= Sum([RunTot_Quantity])> 0"}>} Quantity)

Cheers,

Luiz

Re: Using two conditions in set analysis

Husky_Bab12 — Thu, 18 Aug 2022 17:40:42 GMT

I tried its not working.

Sorry i should have explained properly.

I need to sum quantity even when either of field is 0 values.

The above gives me sum of both arrived run total and run total quantity.

But its not giving total sum when either of the field is not present.

Expected,
arrived = 100, run total=50 , total 150

arrived = 0. run = 150 , total 150

arrived = 15, run = 0, total 15

Your formula,

total= 150

total=0

total=0

Re: Using two conditions in set analysis

luizcdepaula — Thu, 18 Aug 2022 18:42:44 GMT

=sum({<[Arrived Run Total]={"= Sum([Arrived Run Total])>= 0"} , [RunTot_Quantity]={"= Sum([RunTot_Quantity])>= 0"}>} Quantity)

Re: Using two conditions in set analysis

Digvijay_Singh — Thu, 18 Aug 2022 19:39:43 GMT

Do you want to add whichever has total sum positive?

=sum({<[Arrived Run Total]={"= Sum([Arrived Run Total])>= 0"} > +

<[RunTot_Quantity]={"= Sum([RunTot_Quantity])>= 0"}>} Quantity)

Re: Using two conditions in set analysis

Husky_Bab12 — Thu, 18 Aug 2022 20:19:56 GMT

Yes, thank you for sharing the answer.

Re: Using two conditions in set analysis

Husky_Bab12 — Thu, 18 Aug 2022 20:43:20 GMT

Now, I need to add the sum of quantity which don't have total sum positive. How can i modify the above formula?

Re: Using two conditions in set analysis

Digvijay_Singh — Thu, 18 Aug 2022 21:00:01 GMT

not sure if this is what expected -

=sum({<[Arrived Run Total]={"= Sum([Arrived Run Total])< 0"} > +

<[RunTot_Quantity]={"= Sum([RunTot_Quantity])< 0"}>} Quantity)

Re: Using two conditions in set analysis

Husky_Bab12 — Thu, 18 Aug 2022 21:15:48 GMT

I tried that it gives me entire sum of quantity. expected - we have total sum negative only values

Re: Using two conditions in set analysis

luizcdepaula — Fri, 19 Aug 2022 20:21:33 GMT

Is that an issue or the negative values on the sum is expected as well?

Re: Using two conditions in set analysis

Husky_Bab12 — Mon, 29 Aug 2022 13:20:03 GMT

Yes, I negative value sums are expected. With the above formula I can get positive value sums.