Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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 Smiley Sad

Thanks in advance!!

- Ashish

4 Replies
MVP
MVP

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.

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

Regards

Not applicable

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



vupen
Contributor

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.

Not applicable

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

Community Browser