Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 .
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.
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
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.
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
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.
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.
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.
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
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?!