NESTED SUM WITH IF

[jood_ahmad]

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 .

[Oleg_Troyansky]

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