Use subtraction between columns for Set Analysis

[JustinDallas]

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.

[sunny_talwar]

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

[maxgro · Accepted solution]

if you have an orderid you can try

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

[JustinDallas]

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

[JustinDallas]

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.

[maxgro]

see attachment, page 14

order id with date diffference >= 7