topic Re: Alternative to nested If statements in QlikView

Alternative to nested If statements

Mon, 19 Nov 2012 10:55:43 GMT

Hi Everyone

I've been trying to figure this one out for a while and am having no luck. I wish to get rid of my nested if statments as I have alot.

On one of the graphs I have created I have allowed it to be as felxible as possible allowing the user to view anything against anything.

There is a selection box to choose the how the data is put together, ie median, avg, top 10 sales, bottom 10 sales. They then can filter this based on selection boxes that allows them to choose what data set is used.

So the user can effectivly compare top 10 sales in Europe against all sales in the world.

I've done this however using nested if statements (60 in total!) and I'm trying to figure out a more efficient way.

code for eMidSizeExpressions

(vSalesExpressions = 'Sale Size M',Avg({$< [Sale Size]= {'mid Sale'} >}RelativeReturnPc) ,

if(vSalesExpressions1 = 'Sale Size M',Avg({$< [Salel Size]= {'mid Sale'} >}RelativeReturnPc) ,

if(vSalesExpressions2 = 'Sale Size M',Avg({$< [Sale Size]= {'mid Sale'} >}RelativeReturnPc) ,

if(vSalesExpressions3 = 'Sale Size M',Avg({$< [Sale Size]= {'mid Sale'} >}RelativeReturnPc) ,

if(vSalesExpressions4 = 'Sale Size M',Avg({$< [Sale Size]= {'mid Sale'} >}RelativeReturnPc) ,

if(vSalesExpressions5 = 'Sale Size M',Avg({$< [Sale Size]= {'mid Sale'} >}RelativeReturnPc) ,

if(vSalesExpressions6 = 'Sale Size M',Avg({$< [Sale Size]= {'mid Sale'} >}RelativeReturnPc) ,

))))))

vSalesExpressionsX - is set by the input box by the user and detemines which expression is read.

If one of the input boxes is set to 'Sale Size Mid' then the variable vSalesExpressionsX is set to 'Sale Size Mid'. eMidSizeExpression is set to the expression through the above code and passed through to the graphs expression field.

if an alternative method is available to allow a user to choose what each line in the graph shows that would be welcome too,

Thanks!!!

Re: Alternative to nested If statements

somenathroy — Mon, 19 Nov 2012 11:23:26 GMT

Dear Friend,

I think it needs some extra clarification regarding your query and also about the data model. Because the expression's output would not be change depending upon the user's input as given.

Regards,

som

Re: Alternative to nested If statements

Gysbert_Wassenaar — Mon, 19 Nov 2012 12:24:28 GMT

try: if(Match('Sale Size M',vSalesExpressions1,vSalesExpressions2,vSalesExpressions3, ...... ,vSalesExpressionsN), Avg({$< [Sale Size]= {'mid Sale'} >}RelativeReturnPc))

Re: Alternative to nested If statements

Mon, 19 Nov 2012 13:43:22 GMT

Thanks

Thanks the match function worked perfectly.

To Som, to make it change by the users selection I've included a set thats determined by the users input in the selectionbox using variavles. ie:

Avg({$< [Sale Size]= {'mid Sale'}, country ={$(=vReturnsLine1Country)}, >}RelativeReturnPc))

thanks for your help!!

Re: Alternative to nested If statements

Mon, 19 Nov 2012 17:37:11 GMT

Hi,

I've changed it to a pick(match) statement as it seemed more applicable, however following previous posts I cant seem to get mine to work.

vSalesExpressions- is still set by an input box to one of the strings defined, I have put this expression as an expression in a line graph.

pick(Match($(vSalesExpressions),'Avg Sales','Median
Sales','Avg Winning Products','Avg Loosing Products','Top 10 Sales','Bottom 10 Sales'),

Avg(ProfitPc),

Median(ProfitPc),

Avg({1<profitindicator = {1} >}ProfitPc),

Avg({1<profitindicator = {0} >}ProfitPc),

Avg({1<SalesPerson = {"=Rank(aggr(avg(ProfitPc),
SalesPerson)) <= 10"}>}ProfitPc),

Avg({1<SalesId = {"=Rank(aggr(-avg(ProfitPc),SalesId))<=10 "}>} ProfitPc)

)