topic Set Analysis use OR in QlikView

Set Analysis use OR

Thu, 17 Feb 2011 18:07:43 GMT

I am trying to show data for only yesterday and today. I have a variable that calculates yesterday's date vMaxDate. I want all sales where the Date = Yesterday OR the date = Today.

vMaxDate is yesterday because that is our report cutoff BUT some sales come thru with today's date so I want to see those as well. I am using v9

I used the below but it only picks up yesterday's sales, not todays. What am I doing wrong?

sum({<Date ={"$(#vMaxDate)","$(#vMaxDate)+1"} ,[Subscription Indicator]={"Non-Subscription"}>} [TOTAL BOOKED ($)])/1000

Set Analysis use OR

matt_crowther — Thu, 17 Feb 2011 18:52:45 GMT

Unchecked expression that should in principal work:

=sum(if(Date=today() or Date = today()-1,Sales))

You may have to put date() functions round the 'Date' and today to ensure they're the same format but there's no reason that shouldn't work.

It should then be a simple(ish) process to get it into Set Analysis.

Hope that helps,

Matt - Visual Analytics Ltd

Set Analysis use OR

pover — Thu, 17 Feb 2011 19:10:01 GMT

Matt's solution should work, but just so that you understand set analysis, you need to put the formula within the dollar-sign expansion $() like the following:

sum({<Date ={"$(vMaxDate)","$(=vMaxDate+1)"} ,[Subscription Indicator]={"Non-Subscription"}>} [TOTAL BOOKED ($)])/1000

And you might be careful that the MaxDate variable returns a value in the date format like

date(max(Date))

Regards.