Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jood_ahmad
Creator II
Creator II

NESTED SUM WITH IF

dears,

if i have this data :

  

%Order_KeyPROMOFLAGITEM_DESCRIPTIONTOTAL_AMOUNT
43103PROMOBBQ Chicken Large64
43103PROMOBuffalo Chicken Large64
43103PROMODeluxe Large64
43103NOT PROMOMargarita Large30
43103NOT PROMOMirinda2
43110NOT PROMORanchy Original Medium20
43110NOT PROMORanchy Original No Jalapeno M.20
43111PROMOBBQ Chicken Small5
43111PROMOBBQ Chicken Wings9
43111NOT PROMOMirinda Citrus2
43112NOT PROMOGrilled Chicken Medium20
43112NOT PROMORanchy Sunset Large30
43113NOT PROMOQuesadilla BBQ18
43113PROMORanch Dip3
43113PROMOTomato Sauce Dip3

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 .

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

View solution in original post

10 Replies
Frank_Hartmann
Master II
Master II

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

jood_ahmad
Creator II
Creator II
Author

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.

Frank_Hartmann
Master II
Master II

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

jood_ahmad
Creator II
Creator II
Author

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.

jood_ahmad
Creator II
Creator II
Author

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.

it look perfect and simple.

Frank_Hartmann
Master II
Master II

have a look at this thread:

Books and literature

hope this helps

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

jood_ahmad
Creator II
Creator II
Author

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?!