Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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%)'
)
)
)
)
Thanks for taking time to reply , unfortunately that didnt worked Fei Xu
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])
Hi Anand
Try testing one step a the time in each If(), to find the line that is uncorrect.
/Teis
What is the error you are getting.
@@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.