I would like a pivot table column (just one column) to reduce some of the data similar to how selecting on a list box or applying a bookmark does. I tried rewriting my logic in a few different ways but keeping getting the same solution. I have a feeling it is not fully applying my logic.
I would like Orders in this column to have complete/completed flag = N and the below dispositions to be meet.
=sum(if(complete='N' OR completed='N' OR disposition = 'B' OR disposition = 'D' OR disposition = 'null' OR disposition = 'S', (qty_ordered_Ord-qty_canceled_Ord)*unit_price_Ord))
Is this correct way of writing this? Any other ideas? I am not opposed to use Set Analysis I just find using if then logic to be easier.
The if tends to duplicate data if the condition is not at the same detail level as the fields you are summing, so I would try NMiller's suggestion because the logic might get complicated in set analysis. I think it would be something like the following:
That way works fine. Set Analysis should make expressions faster and easier to work with. That being said, if your expression is working, it isn't causing any delays and you understand exactly how it works, then you're probably doing it right. I wouldn't switch it to Set Analysis unless there was a valid reason. There may be a QlikView function that could handle that logic, but since there are three different fields being evaluated, the if is probably a good way to do it.
If Unit_Proce_Ord is a global value (not different from record to record), then taking it out of the Sum may increase the speed.
Nothing is wrong with the formula, but are you sure you don't want to put an "AND" between completed and disposition? I understood this in your plain English explanation "to have the complete/completed flag = N and the below dispositions to be met"
=sum(if( (complete='N' OR completed='N') AND (disposition = 'B' OR disposition = 'D' OR disposition = 'null' OR disposition = 'S'), (qty_ordered_Ord-qty_canceled_Ord)*unit_price_Ord))
or easier
=sum(if( (complete='N' OR completed='N') AND (match(disposition,'B','D','null','S')), (qty_ordered_Ord-qty_canceled_Ord)*unit_price_Ord))
Be careful with the null value. If null is a string the above formula is ok, but if it is the null value then use the function isnull()
Okay this is 'working' but I realize it is doing the math incorrectly.
//equals $28,714 correct with 'flags' applied to it =money(sum((qty_ordered_Ord-qty_canceled_Ord)*unit_price_Ord)) //equals $114,856 inside pivot table it is summing each 'line item' on order. This order happens to have 4 line items to it adding all four of these =money(sum(if( (complete='N' AND completed='N') AND (match(disposition,'B','D','null','S')), (extPrice_Ord -(comCost_Ord*qty_ordered_Ord)))))
What is your order key field called? It looks like it is quadrupling the value and you just want the normal value? Aggr() by Order Key.
=money(Aggr(sum( if( (complete='N' AND completed='N') AND (match(disposition,'B','D','null','S')), (extPrice_Ord -(comCost_Ord*qty_ordered_Ord))), ORDER_KEY))
Order_no is the key. This is not working. Cause each order_no has multiple line items?
=money(Aggr(sum( if( (complete='N' AND completed='N') AND (match(disposition,'B','D','null','S')), (extPrice_Ord -(comCost_Ord*qty_ordered_Ord)))), order_no))
The if tends to duplicate data if the condition is not at the same detail level as the fields you are summing, so I would try NMiller's suggestion because the logic might get complicated in set analysis. I think it would be something like the following:
Hmm. Somewhat closer. Now it is not displaying all the order amounts as it should. Not sure what it is missing. Playing around with the set analysis. Nmiller did not calculate when I put the order_no as the key. See above post.
