Set analysis instead of if statements

[#1]

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

[Miguel_Angel_Baeyens]

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

[#3]

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

[vupen]

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.

[#5]

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