Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

if statement in set analysis

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)

1 Solution

Accepted Solutions
Highlighted

Re: if statement in set analysis

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


View solution in original post

6 Replies
Highlighted
Creator III
Creator III

Re: if statement in set analysis

cud u share your qvw

Highlighted
Creator
Creator

Re: if statement in set analysis

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?

Highlighted
Creator III
Creator III

Re: if statement in set analysis

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/

Highlighted

Re: if statement in set analysis

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


View solution in original post

Highlighted
Not applicable

Re: if statement in set analysis

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

Highlighted
Not applicable

Re: if statement in set analysis

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