Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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
cud u share your qvw
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?
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/
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
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
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