4 Replies Latest reply: Apr 6, 2010 10:51 AM by ashish_w RSS

    Set analysis instead of if statements

    ashish_w

      Hi,

      I am quite new to set analysis. Many of the posts say that set analysis is much faster than if statements. I have a question regarding using set analysis instead of IF for performance improvement.

      In IF we can give else part, how can we achieve the same in set analysis. I have huge set of IF in an expression of one of my pivot tables which is causing performance issue :(

      Thanks in advance!!

      - Ashish

       

       

        • Set analysis instead of if statements
          Miguel Angel Baeyens de Arce

          Hello Ashish,

          There is no real "else" for set analysis, because what you use in set analysis is a condition the formula must match to aggregate. But you can use that conditional in the opposite, for example:

          Sum({< Year = {2008} >} Sales)
          will sum Sales where field Year has value '2008'. And you can use
          Sum({< Year -= {2008} >} Sales)
          to sum Sales of all years except for '2008'. There may be some other best code examples but, basically, this is how it works.

          Can you post any of your conditionals to help you more accurately?

          Regards

            • Set analysis instead of if statements
              ashish_w

               

              Actually I am not sure if it is easily understandable. But following is the expression which is causing performance issue.

              Type_tab3 contains column name as a result of crosstable.

              =if(index(Type_tab3,'VAR',1),
              if(index(Type_tab3,'CASE',1),
              if(index(Type_tab3,'4W',1),if(sum(LY_4W_POS_CASE_3)=0, null(), text(num(((sum(Y_4W_POS_CASE_3)-sum(LY_4W_POS_CASE_3))/sum(LY_4W_POS_CASE_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'8W',1),if(sum(LY_8W_POS_CASE_3)=0,null(),text(num(((sum(Y_8W_POS_CASE_3)-sum(LY_8W_POS_CASE_3))/sum(LY_8W_POS_CASE_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'13W',1),if(sum(LY_13W_POS_CASE_3)=0,null(),text(num(((sum(Y_13W_POS_CASE_3)-sum(LY_13W_POS_CASE_3))/sum(LY_13W_POS_CASE_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'26W',1),if(sum(LY_26W_POS_CASE_3)=0,null(),text(num(((sum(Y_26W_POS_CASE_3)-sum(LY_26W_POS_CASE_3))/sum(LY_26W_POS_CASE_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'52W',1),if(sum(LY_52W_POS_CASE_3)=0,null(),text(num(((sum(Y_52W_POS_CASE_3)-sum(LY_52W_POS_CASE_3))/sum(LY_52W_POS_CASE_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'YTD',1),if(sum(LYTD_POS_CASE_3)=0,null(),text(num(((sum(YTD_POS_CASE_3)-sum(LYTD_POS_CASE_3))/sum(LYTD_POS_CASE_3))*100,'##,###.##') & '%')),if(sum(LY_W_POS_CASE_3)=0,null(),text(num(((sum(Y_W_POS_CASE_3)-sum(LY_W_POS_CASE_3))/sum(LY_W_POS_CASE_3))*100,'##,###.##') & '%'))
              )))))),
              if(index(Type_tab3,'DOLLAR',1),
              if(index(Type_tab3,'4W',1),if(sum(LY_4W_POS_DOLLARS_3)=0,null(),text(num(((sum(Y_4W_POS_DOLLARS_3)-sum(LY_4W_POS_DOLLARS_3))/sum(LY_4W_POS_DOLLARS_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'8W',1),if(sum(LY_8W_POS_DOLLARS_3)=0,null(),text(num(((sum(Y_8W_POS_DOLLARS_3)-sum(LY_8W_POS_DOLLARS_3))/sum(LY_8W_POS_DOLLARS_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'13W',1),if(sum(LY_13W_POS_DOLLARS_3)=0,null(),text(num(((sum(Y_13W_POS_DOLLARS_3)-sum(LY_13W_POS_DOLLARS_3))/sum(LY_13W_POS_DOLLARS_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'26W',1),if(sum(LY_26W_POS_DOLLARS_3)=0,null(),text(num(((sum(Y_26W_POS_DOLLARS_3)-sum(LY_26W_POS_DOLLARS_3))/sum(LY_26W_POS_DOLLARS_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'52W',1),if(sum(LY_52W_POS_DOLLARS_3)=0,null(),text(num(((sum(Y_52W_POS_DOLLARS_3)-sum(LY_52W_POS_DOLLARS_3))/sum(LY_52W_POS_DOLLARS_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'YTD',1),if(sum(LYTD_POS_DOLLARS_3)=0,null(),text(num(((sum(YTD_POS_DOLLARS_3)-sum(LYTD_POS_DOLLARS_3))/sum(LYTD_POS_DOLLARS_3))*100,'##,###.##') & '%')),if(sum(LY_W_POS_DOLLARS_3)=0,null(),text(num(((sum(Y_W_POS_DOLLARS_3)-sum(LY_W_POS_DOLLARS_3))/sum(LY_W_POS_DOLLARS_3))*100,'##,###.##') & '%'))
              )))))),
              if(index(Type_tab3,'4W',1),if(sum(LY_4W_VT_CNT_3)=0,null(),text(num(((sum(Y_4W_VT_CNT_3)-sum(LY_4W_VT_CNT_3))/sum(LY_4W_VT_CNT_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'8W',1),if(sum(LY_8W_VT_CNT_3)=0,null(),text(num(((sum(Y_8W_VT_CNT_3)-sum(LY_8W_VT_CNT_3))/sum(LY_8W_VT_CNT_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'13W',1),if(sum(LY_13W_VT_CNT_3)=0,null(),text(num(((sum(Y_13W_VT_CNT_3)-sum(LY_13W_VT_CNT_3))/sum(LY_13W_VT_CNT_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'26W',1),if(sum(LY_26W_VT_CNT_3)=0,null(),text(num(((sum(Y_26W_VT_CNT_3)-sum(LY_26W_VT_CNT_3))/sum(LY_26W_VT_CNT_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'52W',1),if(sum(LY_52W_VT_CNT_3)=0,null(),text(num(((sum(Y_52W_VT_CNT_3)-sum(LY_52W_VT_CNT_3))/sum(LY_52W_VT_CNT_3))*100,'##,###.##') & '%')),
              if(index(Type_tab3,'YTD',1),if(sum(LYTD_VT_CNT_3)=0,null(),text(num(((sum(YTD_VT_CNT_3)-sum(LYTD_VT_CNT_3))/sum(LYTD_VT_CNT_3))*100,'##,###.##') & '%')),if(sum(LYTD_VT_CNT_3)=0,null(),text(num(((sum(Y_W_VT_CNT_3)-sum(LY_W_VT_CNT_3))/sum(LY_W_VT_CNT_3))*100,'##,###.##') & '%'))
              )))))))),

              if(index(Type_tab3,'DOLLAR',1),if(Metric_tab3='-',null(),text('$' & num(Sum(Metric_tab3),'##,###.##'))),
              if(index(Type_tab3,'VT_CNT',1) OR index(Type_tab3,'CASE',1),if(Metric_tab3='-',null(),text(num(Sum(Metric_tab3),'#,###,###'))),

              if(Metric_tab3='-',null(),num(Sum(Metric_tab3),'##,###.##')))))