Set analysis instead of if statements

Tue, 06 Apr 2010 09:49:58 GMT

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 — Tue, 06 Apr 2010 10:43:05 GMT

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

Tue, 06 Apr 2010 11:56:09 GMT

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

vupen — Tue, 06 Apr 2010 13:09:29 GMT

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

Tue, 06 Apr 2010 14:51:49 GMT

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

topic Set analysis instead of if statements in QlikView

Set analysis instead of if statements

Set analysis instead of if statements

Set analysis instead of if statements

Set analysis instead of if statements

Set analysis instead of if statements