2 Replies Latest reply: Feb 17, 2011 2:10 PM by Karl Pover RSS

    Set Analysis use OR



      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

          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
              Karl Pover

              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