topic Re: NESTED SUM WITH IF in QlikView

NESTED SUM WITH IF

jood_ahmad — Sun, 23 Oct 2016 12:36:39 GMT

dears,

if i have this data :

%Order_Key	PROMOFLAG	ITEM_DESCRIPTION	TOTAL_AMOUNT
43103	PROMO	BBQ Chicken Large	64
43103	PROMO	Buffalo Chicken Large	64
43103	PROMO	Deluxe Large	64
43103	NOT PROMO	Margarita Large	30
43103	NOT PROMO	Mirinda	2
43110	NOT PROMO	Ranchy Original Medium	20
43110	NOT PROMO	Ranchy Original No Jalapeno M.	20
43111	PROMO	BBQ Chicken Small	5
43111	PROMO	BBQ Chicken Wings	9
43111	NOT PROMO	Mirinda Citrus	2
43112	NOT PROMO	Grilled Chicken Medium	20
43112	NOT PROMO	Ranchy Sunset Large	30
43113	NOT PROMO	Quesadilla BBQ	18
43113	PROMO	Ranch Dip	3
43113	PROMO	Tomato Sauce Dip	3

and i need to get the sum in text box for each %Order_Key to define what i sold for Promo and Not Promo in the same order.

example: this order(43103) has Promo & Not Promo i need to get the Total Amount for Promo & NOT Promo Individual like this: Promo:192 . NOT PROMO: 32 .

but orders # (43110 , 43112) don't have Promo so it should not appear on my Total Not Promo because the only thing that i need to see is the total promo and in another text only the total of Not Promo in the orders has Promo(Extra Sales).

I get this result only when i select the order but when it was over all orders the formula not working and i gut confused .

Re: NESTED SUM WITH IF

Frank_Hartmann — Sun, 23 Oct 2016 12:44:41 GMT

For 43103 try in first textbox:

=sum(aggr(sum({<PROMOFLAG={'NOT PROMO'},%Order_Key={'43103'}>}TOTAL_AMOUNT),%Order_Key))

and in second textbox:

=sum(aggr(sum({<PROMOFLAG={'PROMO'},%Order_Key={'43103'}>}TOTAL_AMOUNT),%Order_Key))

hope this helps

Re: NESTED SUM WITH IF

jood_ahmad — Sun, 23 Oct 2016 13:15:07 GMT

thank you Frank, it is work with me like this but the main issue that i face it is when i apply the formula for all not specific %Order_Key it is not working it give me all 'NOT Prom' amount not only orders have promo item and not promo item.

for all NOT PROMO amount as the example it must give me 52.

Re: NESTED SUM WITH IF

Frank_Hartmann — Sun, 23 Oct 2016 13:39:31 GMT

For the "52" try in textbox like:

=sum(aggr(if(sum(aggr(sum({<PROMOFLAG={'PROMO'}>}TOTAL_AMOUNT),%Order_Key))>0,

sum(aggr(sum({<PROMOFLAG={'NOT PROMO'}>}TOTAL_AMOUNT),%Order_Key))),%Order_Key))

hope this helps

Re: NESTED SUM WITH IF

Oleg_Troyansky — Sun, 23 Oct 2016 15:50:38 GMT

I think this is way more complicated than it should be. The same logic could be resolved with a single SUM and a Set Analysis condition with an advanced search. Something along these lines:

SUM(

{<%Order_Key={"=sum({<PROMOFLAG={'PROMO'}>}TOTAL_AMOUNT)>0"} ,

PROMOFLAG={'NOT PROMO'}>}

TOTAL_AMOUNT)

cheers,

Oleg Troyansky

Learn advanced QlikView and Qlik Sense techniques in my book QlikView Your Business.

Re: NESTED SUM WITH IF

jood_ahmad — Mon, 24 Oct 2016 07:28:43 GMT

Thank you i will try it now but can you explain to me the logic that you use it in this formula, actually i'm not that good in set analysis, and how can i become good in set analysis is there any book or exercise to improve my knowledge.

Re: NESTED SUM WITH IF

jood_ahmad — Mon, 24 Oct 2016 07:29:53 GMT

it look perfect and simple.

Re: NESTED SUM WITH IF

Frank_Hartmann — Mon, 24 Oct 2016 08:14:07 GMT

have a look at this thread:

Books and literature

hope this helps

Re: NESTED SUM WITH IF

Oleg_Troyansky — Mon, 24 Oct 2016 14:41:52 GMT

Hi Ahmad,

I listed my book QlikView My Business in my signature. This book is providing a comprehensive tutorial that includes a very deep dive into Set Analysis, Advanced Search, Advanced Aggregation, and many other advanced QlikView and Qlik Sense techniques. This is the only book in the market that starts from the beginners level and takes you deep into the advanced material. All other books are either beginners books or advanced books, but not both.

hope you enjoy it!

cheers,

Oleg Troyansky

Re: NESTED SUM WITH IF

jood_ahmad — Tue, 25 Oct 2016 08:01:03 GMT

Thank you very much Oleg, and i will take your book from Amazon ;

can you explain to me the Expression please , how the qv accept this expression some time i wrote like this and it is not work. you use the %Order_Key in the beginning ; like you use it the %Order_Key as pointer to go through it to check the total amount if it > 0 for the promo and it will give me the total amount for NOT PROMO, is this right?!

Re: NESTED SUM WITH IF

Oleg_Troyansky — Tue, 25 Oct 2016 15:31:03 GMT

Hi Ahmad,

yes, you got it right. Using Advanced Search in Set Analysis, you can verify any "condition" for any field. Based on your explanation, you wanted to calculate totals per %Order_Key that satisfied certain conditions. For that purpose, I picked the same field for the Advanced Search condition.

cheers,

Oleg Troyansky