Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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......
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.
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...
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
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)))
Yes, if You don't filter negative values, then use <> instead >.
Regards,
Antonio