topic Re: [ASK] Create Yearly Trend from Formula? in QlikView

[ASK] Create Yearly Trend from Formula?

rendiyan — Mon, 30 Nov 2015 09:58:38 GMT

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

Re: [ASK] Create Yearly Trend from Formula?

swuehl — Mon, 30 Nov 2015 16:53:17 GMT

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.

Re: [ASK] Create Yearly Trend from Formula?

rendiyan — Tue, 01 Dec 2015 06:36:47 GMT

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 :

Year //for x axis
Description

Expression :

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

so, is there any solution for this issue?

Thanks

Re: [ASK] Create Yearly Trend from Formula?

swuehl — Tue, 01 Dec 2015 18:08:10 GMT

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.