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

# Set analysis instead of if statements

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 :(

- Ashish

• ###### Set analysis instead of if statements

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.

Regards

• ###### Set analysis instead of if statements

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),'##,###.##')))))

• ###### Set analysis instead of if statements

I guess the expression is complex enough on the chart side. Push this expression to the script side. The result is instantaneous and this would become just another column.

You could use Peek function if the base table of the dimension is different from the expression table.

• ###### Set analysis instead of if statements

Hi,

Here i can't push this expression to script side. Because, the pivot table uses a crosstable for having combined column header that is column header for two or more columns. and hence the script is complicated!!

Thanks,

Ashish