topic Pivot Table If Expression in QlikView

Pivot Table If Expression

Tue, 15 Feb 2011 22:34:26 GMT

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.

Thanks for any help you are able to provide.

Pivot Table If Expression

Tue, 15 Feb 2011 23:02:49 GMT

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.

Pivot Table If Expression

pover — Tue, 15 Feb 2011 23:04:45 GMT

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()

Regards.

Pivot Table If Expression

Thu, 17 Feb 2011 17:14:17 GMT

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)))))

Any ideas?

Pivot Table If Expression

Thu, 17 Feb 2011 17:36:52 GMT

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))

Pivot Table If Expression

Thu, 17 Feb 2011 17:45:11 GMT

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))

Pivot Table If Expression

pover — Thu, 17 Feb 2011 17:47:20 GMT

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:

sum( { $<complete={'N'}, disposition={'B','D','null','S'}> + $<completed={'N'}, disposition={'B','D','null','S'}>} (qty_ordered_Ord-qty_canceled_Ord)*unit_price_Ord))

You might be able to simplify the logic.

Regards.

Pivot Table If Expression

Thu, 17 Feb 2011 18:04:13 GMT

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.



money(sum( { $<complete={'N'}, disposition={'B','D','Null','S'}> + $<completed={'N'}, disposition={'B','D','Null','S'}>}
 (qty_ordered_Ord-qty_canceled_Ord)*unit_price_Ord))

Pivot Table If Expression

Thu, 17 Feb 2011 18:08:44 GMT

This worked. Just had to play with (). See below post. Thanks for all your help.