Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

[ASK] Create Yearly Trend from Formula?

Dear Qlik Master,

So i have a report that calculate growth between current year and previous year.

Untitled.png


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

3 Replies
Highlighted
MVP
MVP

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.

Highlighted
Partner
Partner

Sorry for my bad explanation.

So from above chart i have =

Dimension :

Descrption

Expression :

Header on ChartLabelDefinitions
2014=max(Year)sum({<Year={$(=max(Year))}>} values)
2013=max(Year)-1sum({<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 :

  1. Year //for x axis
  2. Description

Expression :

[Growth (Decline) %]  //based on above chart result

so, is there any solution for this issue?

Thanks

Highlighted
MVP
MVP

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.