topic Re: Re: growth calculation in QlikView

growth calculation

Wed, 23 Apr 2014 07:20:00 GMT

Hi,

How if we want to calculate growth in pivot table ?

Group	Month	Sales	Growth
America	1	1000	-
	2	2000	100%
	3	2500	25%
	4	4000	60%
Europe	1	400	-
	2	440	10%

group america ( as dimension ) has 4 details ( Month 1 to 4 ).

100% is calculated from (2000-1000)/1000,

25% is calculated from (2500-2000)/2000

and so on.

1. What is the expression of growth ?

2. is there any demos of .qvw that is similiar like this ?

Re: growth calculation

alexandros17 — Wed, 23 Apr 2014 07:25:14 GMT

write the following as expression

(Sum(Sales)-Above(Sales))/Above(Sales)

let me know

Re: growth calculation

Wed, 23 Apr 2014 07:26:53 GMT

PFA.

The expression is

(Sum(Sales) - Above(Sum(Sales)))/Above(Sum(Sales))

Re: growth calculation

SergeyMak — Wed, 23 Apr 2014 07:31:01 GMT

Hi,

the solution depends on your data.

PFA an example for aggregated month value

I used (Only(Value)-above(Value))/above(Value) in chart

Re: Re: growth calculation

SergeyMak — Wed, 23 Apr 2014 07:41:53 GMT

But if you have not aggregated data by month like this one

Data:

LOAD * INLINE [

Group, Month, Value

America, 1, 1000

America, 2, 1000

America, 3, 500

Europe, 1, 100

Europe, 2, 100

Europe,3, 10

];

you should use other calculation

(rangesum(above(Sum(Value),0,Month))-above(rangesum(above(Sum(Value),0,Month))))

/above(rangesum(above(Sum(Value),0,Month)))

PFA

Re: growth calculation

Wed, 23 Apr 2014 07:56:50 GMT

wow, what a very quick response....thx a lot guys for your answers.

but i didn't see that it is work for analyst.

let say my expression is something like this :

sum({<S_DATE={"= S_DATE >= $(#START_DATE) and S_DATE <= $(#END_DATE)"}>}SALES)/V_DIVIDER

notes : S_DATE = variabel from the field of data

START_DATE = Input field ( Calendar object type that connected to variable )

END_DATE = Input field ( Calendar object type that connected to variable )

SALES = amount of sales ( from field of data )

V_DIVIDER = divider-variable ( thousands, millions, etc )

on the dimension America, there will not always be month 1, 2, 3, 4 ( depends on the date inputted by user, that's the reason why i use analyst ).

let say the month that showed up is month 1, 3 and 4...

i would like to have growth calculation from sales of month 3 subtracted to sales of month 1

month 1, sales = 1000, result : -

month 3, sales = 1200, result 20%

month 4, sales = 2400, result 100%

how do i fix this ?

Re: Re: growth calculation

SergeyMak — Wed, 23 Apr 2014 08:08:05 GMT

But what as for data. You have aggregated values in raw data or not?

My last example works if there're missed month..

PFA

You just need to add calendar object or calendar table...