topic Re: if statement in set analysis in QlikView

if statement in set analysis

yelin_nyu — Fri, 13 Mar 2015 20:43:33 GMT

depending on what user pick, option One or option Two, if user pick option 1, all date will be taken into consideration. if user pick option 2, only dates after vTargetDate are considered.

my original set analysis, regardless of option One or Two, only dates after vTargetDate are considered

$(=vOption) is a selection, users choose option One or Two. and each option has value Y/N

sum({<FiscalYear={"$(=vFY)"}, $(=vOption)={Y},Date={">$(=vTargetDate)"}>}Sales)

I tried below expression, but it doesn't work.wrong syntax? please help

sum({<FiscalYear={"$(=vFY)"}, $(=vOption)={Y},if(vOption='Two', Date={">$(=vTargetDate)"},Date=)>}Sales)

Re: if statement in set analysis

Anonymous — Fri, 13 Mar 2015 20:55:01 GMT

cud u share your qvw

Re: if statement in set analysis

yelin_nyu — Fri, 13 Mar 2015 21:08:12 GMT

sorry the file is too big to share.

the data set basically look like below

date, OptionOne, OptionTwo, sales

1/1/2015, Y, Y, 100

2/1/2015, Y, N, 200

3/1/2015, N, Y, 150

if user choose to use option one approach, sales will include all dates where option One=Y

100+200=300

if user choose to use option Two approach, sales will only include dates after vTargetDate and option Two=Y, let's assume vTargetDate is 2/1/2015. so only sales that satisfy both condition is 150.

I could write an expression look like this

if(vOption='Two',

sum({<FiscalYear={"$(=vFY)"}, OptionTwo={Y},Date={">$(=vTargetDate)"}>}Sales),

sum({<FiscalYear={"$(=vFY)"}, OptionOne={Y}>}Sales))

it's just very redundant since the only difference in the expression is Date={">$(=vTargetDate)"}

Another method I used is

sum({<FiscalYear={"$(=vFY)"}, $(vOption)={Y}>}if(vOption='Two', if(Date>vTargetDate,Sales), Sales)

I know both would work, but which one will give me better performance?

Re: if statement in set analysis

Anonymous — Sat, 14 Mar 2015 02:14:41 GMT

I think you are on the right track and your first expression seems correct to me. Performance wise, you should always test yourself but generally IF statements inside aggregation functions are not recommended. Outer IFs are fine though.

another technique is to use As of Date tables

http://www.naturalsynergies.com/q-tip-4-how-to-use-as-of-date-table/

Re: if statement in set analysis

sunny_talwar — Sat, 14 Mar 2015 02:28:04 GMT

There is a third method you can give a shot also.

Use another variable say vTD = If(vOption = 'Two', vTargetDate, Min(date)

and then use this expression:

sum({<FiscalYear={"$(=vFY)"}, $(=vOption)={Y},Date={">$(=vTD)"}>}Sales)

I have not tested the expression, so you might have to make some changes.

HTH

Best,

Re: if statement in set analysis

Sat, 14 Mar 2015 03:47:13 GMT

your expression is incorrect sum({<FiscalYear={"$(=vFY)"}, $(=vOption)={Y},if(vOption='Two', Date={">$(=vTargetDate)"},Date=)>}Sales)

you have used $(=vOption)={Y} but here RHS value is not calculated so thats why you didnt get your correct answer

Re: if statement in set analysis

Sat, 14 Mar 2015 03:52:59 GMT

you can try following expression

if(Option=1,sum({<FiscalYear={"$(=vFY)"},Date={">=$(=vTargetDate)"}>}Sales),if(Option=2,sum({1<FiscalYear={"$(=vFY)"},>}Sales,sum(Sales))

and for more detail post your sample QVW file