if statement in set analysis

[yelin_nyu]

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)

[Anonymous]

cud u share your qvw

[yelin_nyu]

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?

[Anonymous]

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/

[sunny_talwar · Accepted solution]

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,

S

[#6]

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

[#7]

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