Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlik Master,
So i have a report that calculate growth between current year and previous year.
I'm using set analysis for 2014 and 2013 values.
Growth based on 2014-2013.
Growth % based on Growth/2013.
And my client want to see yearly trend based on Growth %.
Is there any way to create trend based on growth trend?
Thanks,
Best Regards
It's easier to help you if you post a small sample QVW or add more detailed explanations about your data model, dimensions and expressions used in that chart.
If you want to show a trend over years, start with a line chart, use Year field as dimension.
You may want to use a second dimension to show a line for each Description in your above chart. For now I assume this field is called Description.
Then I assume you calculate the amount for a selected Year / Description just like Sum(Amount).
Your Expression in the line chart to show the growth trend could then look like
= Num( Aggr( Sum(Amount) / Above(Sum(Amount)) -1 ,Description, Year) ,'#.00%')
To make this work, the Year field needs to have a chronological LOAD order.
Sorry for my bad explanation.
So from above chart i have =
Dimension :
Descrption
Expression :
Header on Chart | Label | Definitions |
---|---|---|
2014 | =max(Year) | sum({<Year={$(=max(Year))}>} values) |
2013 | =max(Year)-1 | sum({<Year={$(=max(Year)-1)}>} values) |
Growth (Decline) | Growth (Decline) | Column(1)-Column(2) |
Growth (Decline) % | Growth (Decline) % | Column(3)/column(2) |
Based on above chart, a client request a chart with =
Chart Type :
Line Chart
Dimensions :
Expression :
[Growth (Decline) %] //based on above chart result
so, is there any solution for this issue?
Thanks
Yes, it should work with the expression
= Num( Aggr( Sum(values) / Above(Sum(values)) -1 ,Description, Year) ,'#.00%')
You just need to have your Year values in chronological LOAD order, e.g. by loading the field first in your script:
TMP:
LOAD recno() as Year
Autogenerate 3000; //assuming Year value range 1 to 3000 A.D. should be sufficient
Then follows your current script, loading your Year field from your sources.
...
At the end:
DROP TABLE TMP;
If that's not working, please post a small sample QVW with your current status.