set analysis expression -- using if sentence under 2 scenarios

Tue, 15 Apr 2014 16:10:38 GMT

Hello all, I have a question about using if statement and set analysis to calculate sales:

the calculation are under 2 scenarios, one is past_trips > 0 and isnull(cancellation_date) = 1; one is past_trips > 0 and isnull(cancellation_date) = 0

then we calculate the total with set values for certain variables, the expression is as follows:

if(past_trips > 0 and isnull(cancellation_date) = 1,

sum({<fiscal = {'$(vLastFY)'},

[deposit_date] = {"<=$(=TimeStamp(vToday_Y-1))"}>} 1))

if(past_trips > 0 and isnull(cancellation_date) = 0,

sum({<fiscal = {'$(vLastFY)'},

[deposit_date] = {"<=$(=TimeStamp(vToday_Y-1))"},

[cancellation_date] = {">=$(=TimeStamp(vToday_Y-1))"}>} 1))

vToday_Y-1 is dynamic variable: =makedate(year(today())-1, month(today()), day(today()))

Is there anything wrong with this expression? Thanks so much for the help!

Xuan

Re: set analysis expression -- using if sentence under 2 scenarios

Tue, 15 Apr 2014 19:10:42 GMT

I solved it, better use set analysis

=count({<fiscal = {'$(vLastFY)'},

[past_trips] -= {'0'},

[deposit_date] = {"<=$(=TimeStamp(vToday_1Y_Ago))"},

[cancellation_date] = {">=$(=TimeStamp(vToday_1Y_Ago))"}>} trip_no) +

count({<fiscal = {'$(vLastFY)'},

[past_trips] -= {'0'},

[deposit_date] = {"<=$(=TimeStamp(vToday_1Y_Ago))"},

[status] = {'BK','WL','RQ'} >} trip_no)

topic set analysis expression -- using if sentence under 2 scenarios in QlikView

set analysis expression -- using if sentence under 2 scenarios

Re: set analysis expression -- using if sentence under 2 scenarios