sales Growth

[paulwalker]

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......

[rubenmarin]

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.

[paulwalker]

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.

like

(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...

[antoniotiman]

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

[paulwalker]

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)))

[antoniotiman]

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

Regards,

Antonio