topic Re: sales Growth in QlikView

sales Growth

paulwalker — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

I have one scenario, I'm calculating Sales Growth .

My calculation: Max(Year)-Max(year-1)/Max(Year-1)

= (2017 sales-2016 sales)/2016 sales

Table:

Client	Year	Sales	Budget
A	2017	2000	15000
B	2017	1400	12500
C	2017	1000	13000
D	2017	1590	17250
E	2017		65000
A	2016	950	25000
B	2016	1200	15870
C	2016	590	12750
D	2016		9000
E	2016		12360

My requirement:

Suppose 1. if i have previous values 0 sales, i have to show 0.01..

2. If i have both values 0.. Max year sales and previous year sales 0, i have to get rid of that..

how to compare data if i have both Zero's ??

I have to get rid of E client data, because both sales have Zero data..

Thanks in Advance......

Re: sales Growth

rubenmarin — Sun, 17 Sep 2017 09:39:54 GMT

Hi Paul, E is there because has data for budget, to avoid this you can change the budget expression to only show budget when both sales are <>0.

Doing this and checking the option to 'Supress zero values' in presentation tab, E should dissapear.

Re: sales Growth

paulwalker — Sun, 17 Sep 2017 17:54:34 GMT

Hi Ruben,

I don't need to show budget, Here I'm calculating YoY sales only.

Suppose i have 2 years data 2017 and 1016

Maximum year 2017

Minimum Year 2016, so my previous year 2016

Now, if we have previous year (2016) sales 0, here taking 0.01, otherwise normal calculation.

(Sum({$<Year={$(=(Max(Year)))}> } Sales) - Sum({$<Year={$(=(Max(Year)-1))}> } Sales))

Sum({$<Year={$(=(Max(Year)-1))}> } Sales))

Suppose if i have max and min year Sales 0, i have to get rid of that client data...

Hope you understand...

Re: sales Growth

antoniotiman — Sun, 17 Sep 2017 18:17:25 GMT

Hi Paul,

maybe this

If(Sum({$<Year={$(=(Max(Year)))}> } Sales) > 0 or Sum({$<Year={$(=(Max(Year)-1))}> } Sales) > 0,
IF(Sum({$<Year={$(=(Max(Year)-1))}> } Sales)=0, 0.01,
(Sum({$<Year={$(=(Max(Year)))}> } Sales) - Sum({$<Year={$(=(Max(Year)-1))}> } Sales))
/
Sum({$<Year={$(=(Max(Year)-1))}> } Sales)))

Regards,

Antonio

Re: sales Growth

paulwalker — Mon, 18 Sep 2017 04:52:06 GMT

Exactly what I'm expecting,

but problem is if i have sales Negative values that will also go...

i think this will work fine

If(Sum({$<Year={$(=(Max(Year)))}> } Sales) <> 0 or Sum({$<Year={$(=(Max(Year)-1))}> } Sales) <> 0,
IF(Sum({$<Year={$(=(Max(Year)-1))}> } Sales)=0, 0.01,
(Sum({$<Year={$(=(Max(Year)))}> } Sales) - Sum({$<Year={$(=(Max(Year)-1))}> } Sales))
/
Sum({$<Year={$(=(Max(Year)-1))}> } Sales)))

Re: sales Growth

antoniotiman — Mon, 18 Sep 2017 05:54:34 GMT

Yes, if You don't filter negative values, then use <> instead >.

Regards,

Antonio