9 Replies Latest reply: Apr 28, 2011 9:19 AM by At titude RSS

    Suggest Set Analysis for 'IF Statement'

      Hi All

      Can some one help me out to write the Set Analysis for the below three IF Statement as the performance of the chart is poor because of this. If not for all the 3 whatever is known please let me know that atleast.

       


      SUM(IF(StartDate<=date AND EndDate>=date,1))


      and

       


      if(only(weekday(date))='Sat' or only(weekday(date))='Sun',darkgray(),
      IF(A1='Change1' ,lightgray(),
      IF(A1='Change2',lightred(),
      IF(A1='Change3',yellow(),
      IF(A1='Change4',lightmagenta(),
      IF(A1='Change5',lightcyan(),
      IF(A1='Change6',lightblue(),
      green())))))))


      and also for this

       


      =if(weekday(date) ='Sat' or weekday(date) ='Sun',darkgray())


        • Suggest Set Analysis for 'IF Statement'
          Miguel Angel Baeyens de Arce

          Hello,

          Your first expression, assuming StartDate and EndDate are fields and "date" a variable should work as

           

          SUM({< StartDate = {'<=$(=Date(date))'}, EndDate {'>=$(=Date(date))'} >} 1)


          The second one is a pure conditional not an aggregation, so it won't work with set analysis. Rather I'd move that condition to the script, and take the value of that field in the color condition. Provided you have a calendar table linked to your fact table, a mapping table should do the trick. And the same for the third, you can always get your weekday in the script, assign the color as value of the field and use it in the chart.

          Hope that helps.

            • Suggest Set Analysis for 'IF Statement'

               


              Miguel Angel Baeyens wrote:
              Your first expression, assuming StartDate and EndDate are fields and "date" a variable should work as <blockquote>SUM({< StartDate = {'<=$(=Date(date))'}, EndDate {'>=$(=Date(date))'} >} 1) </blockquote><div></div>


              I tried the above one but it is not working as expected. May be becuase of the wrong assumption! As per your assumption "date" is not a variable it is field. Can you please do the required changes in the above set analysis.