Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mhappiee
New Contributor III

If expression error

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

6 Replies
xufei123
Valued Contributor

Re: If expression error

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
New Contributor III

Re: If expression error

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

shraddha_g
Honored Contributor III

Re: If expression error

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
Contributor II

Re: If expression error

Hi Anand

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

/Teis

rupamjyotidas
Valued Contributor

Re: If expression error

What is the error you are getting.

mhappiee
New Contributor III

Re: If expression error

@@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.

Community Browser