If expression error

[mhappiee]

Hi Guys,

Could somebody help me why this if expression is not working , is it because qlik doesn't understand the > (greater than )  and <(Less than) symbol?

aggr(

if(((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))

/sum({<Year={$(=max(num(Year))-1)}>}Revenue))<= -1 ,'Lost (100%)',

if(((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))

/sum({<Year={$(=max(num(Year))-1)}>}Revenue)) <= -0.99

and

((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))

/sum({<Year={$(=max(num(Year))-1)}>}Revenue))>= -0.70,'Attrition ( 70% -90%)',

if(((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))

/sum({<Year={$(=max(num(Year))-1)}>}Revenue)) >= -0.29

and

if((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))

/sum({<Year={$(=max(num(Year))-1)}>}Revenue))<= -0.69,'At Risk (20%-69%)',

if(((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))

/sum({<Year={$(=max(num(Year))-1)}>}Revenue))=0,'New Clients','Client Growth (>20%)')))),

[Hqtrs Name])

Thanks

Anand

[NZFei]

try this:

=if(aggr((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))/sum({<Year={$(=max(num(Year))-1)}>}Revenue),[Hqtrs Name])<= -1 ,
'Lost (100%)',
if(aggr((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))/sum({<Year={$(=max(num(Year))-1)}>}Revenue),[Hqtrs Name]) <= -0.99
and
aggr((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))/sum({<Year={$(=max(num(Year))-1)}>}Revenue),[Hqtrs Name])>= -0.70,
'Attrition ( 70% -90%)',
if(aggr((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))/sum({<Year={$(=max(num(Year))-1)}>}Revenue),[Hqtrs Name]) >= -0.29
and
aggr((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))/sum({<Year={$(=max(num(Year))-1)}>}Revenue),[Hqtrs Name])<= -0.69,
'At Risk (20%-69%)',
if(aggr((sum({<Year= {$(=Max(num(Year)))}>}Revenue)-Sum({<Year={$(=max(Num(Year))-1)}>}Revenue))/sum({<Year={$(=max(num(Year))-1)}>}Revenue),[Hqtrs Name])=0,
'New Clients',
'Client Growth (>20%)'
)
)
)
)

[mhappiee]

Thanks for taking time to reply , unfortunately that didnt worked  Fei Xu

[shraddha_g]

Try

aggr(

if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

/sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))<= -1 ,'Lost (100%)',

if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

/sum({<Year={"$(=max(num(Year))-1)"}>}Revenue)) <= -0.99

and

((sum({<Year= {"$(=Max(num(Year)))}">}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)}">}Revenue))

/sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))>= -0.70,'Attrition ( 70% -90%)',

if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

/sum({<Year={"$(=max(num(Year))-1)"}>}Revenue)) >= -0.29

and

if((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

/sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))<= -0.69,'At Risk (20%-69%)',

if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

/sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))=0,'New Clients','Client Growth (>20%)')))),

[Hqtrs Name])

or

aggr(

if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

/sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))<=' -1' ,'Lost (100%)',

if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

/sum({<Year={"$(=max(num(Year))-1)"}>}Revenue)) <= '-0.99'

and

((sum({<Year= {"$(=Max(num(Year)))}">}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)}">}Revenue))

/sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))>= '-0.70','Attrition ( 70% -90%)',

if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

/sum({<Year={"$(=max(num(Year))-1)"}>}Revenue)) >= '-0.29'

and

if((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

/sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))<= '-0.69','At Risk (20%-69%)',

if(((sum({<Year= {"$(=Max(num(Year)))"}>}Revenue)-Sum({<Year={"$(=max(Num(Year))-1)"}>}Revenue))

/sum({<Year={"$(=max(num(Year))-1)"}>}Revenue))='0','New Clients','Client Growth (>20%)')))),

[Hqtrs Name])

[teiswamsler]

Hi Anand

Try testing one step a the time in each If(), to find the line that is uncorrect.

/Teis

[rupamjyotidas]

What is the error you are getting.

[mhappiee]

@@Got it !, But I Have an other issue now

The error is due to

-ve value i.e it should be >= -0.99 inspite of  <= '-0.99' the symbol should be reverse for the -ve values.

I have an other issue now, this is aggregating the value based on the max year for individual record for eg:

If A is having Revenue data for  2016, 2015, 2014 and B is having data for 2015, 2014

the above aggregation is happening at their Max year levels

like for A it is picking 2016 revenue and for B it is picking 2015 revenue and the value i'm getting is wrong

So If I select 2016 in list box it should sum for only 2016 and should take revenue for  A and take 0 value for B

how is this possible

the expression im using is

(sum({<Year#= {$(=Max(num(Year#)))}>}Revenue)-Sum({<Year#={$(=max(Num(Year#))-1)}>}Revenue)

Kind Regards,

Anand.