topic using IF AND OR statements in Set Analysis in QlikView

using IF AND OR statements in Set Analysis

Thu, 08 Sep 2011 10:54:19 GMT

I am trying to figure out a set statement for the following rule

IF [Created Date] <= current select, AND [Close Date] > current select, OR [Order Status] -= 'Cancelled', 'Rejected', sum sales, 0

the end goal is to get a statement that sums up all open orders on any given day.

I don't even know if this is the best way to achieve this but due to the nature of open orders constantly changing I need to be able to have a progressive view on it.

thank you

Re: using IF AND OR statements in Set Analysis

Thu, 08 Sep 2011 11:16:44 GMT

Try something like this:

Sum({<[Created Date]={"<=$(vCurrentSelect)"},[Close Date]={">=$(vCurrentSelect)"}>+<[Order Status-={'Cancelled','Rejected'}>}Sales)

Re: using IF AND OR statements in Set Analysis

swuehl — Thu, 08 Sep 2011 11:16:55 GMT

Hm, maybe like

= sum({<[Created Date] = {"<=$(=max(Date))"},[Close Date] = {">$(=max(Date))"}, [Order Status] -= {'Cancelled','Rejected'}>} Sales)

if Date is your date field to select from. I also assumed a AND [Order status] above given your textual description (all open orders on any given date). If there is only one OPEN status, you could use [Order Status] = {'Open'} instead above part.

Regards,

Stefan

P.S: You probably need to clear some fields also, like Date, more Date fields?

= sum({<[Created Date] = {"<=$(=max(Date))"},[Close Date] = {">$(=max(Date))"}, [Order Status] -= {'Cancelled','Rejected'}, Date= >} Sales)