topic Use subtraction between columns for Set Analysis in QlikView

Use subtraction between columns for Set Analysis

JustinDallas — Wed, 09 Sep 2015 20:14:05 GMT

Hello Everyone,

So I have to use set analysis to create a bar chart with two bars. One bar set will be for Orders that have a "lead time" of 7 days or less, and the other will be for Orders that have a "lead time" of more than 7 days. "Lead Time" is calculated by taking the date the order was entered (OrderEnteredDate) and subtracting it from the promise/delivery date (PromiseDate). So my two set discriminators would resemble (PromiseDate - OrderEnteredDate) > 7 and (PromiseDate - OrderEnteredDate) <= 7.

My current issue is that I don't know how to create a SetAnalysis with subtraction in it. I know how to do "7 days ago" SUM({<PromiseDate ={">=$(=Date(today()-7))"}>} NetSalesDOM), but I don't know how to have it dependent on a calculation between two columns in the same table.

Any help is greatly apprecated.

Re: Use subtraction between columns for Set Analysis

sunny_talwar — Wed, 09 Sep 2015 20:25:06 GMT

Why don't you calculate the difference between the two dates in the script and then use the new field in your set analysis?

Re: Use subtraction between columns for Set Analysis

maxgro — Wed, 09 Sep 2015 20:26:01 GMT

if you have an orderid you can try

sum({$ <OrderId={"=(PromiseDate -OrderEnteredDate)>=7"}>} NetSalesDOM)

Re: Use subtraction between columns for Set Analysis

JustinDallas — Wed, 09 Sep 2015 20:26:28 GMT

That was my first thought, but I was wondering if there was a way to do it in the presentation layer.

Re: Use subtraction between columns for Set Analysis

JustinDallas — Wed, 09 Sep 2015 20:34:32 GMT

I'm confused how this works. It looks like it's saying "if the order id equals the result of this boolean test". What am I missing?

EDIT: Also, I threw it into my dashboard and it appears to work, but I'm not sure why.

Re: Use subtraction between columns for Set Analysis

maxgro — Wed, 09 Sep 2015 20:41:27 GMT

see attachment, page 14

order id with date diffference >= 7