Yearly change in sales

[#1]

Hi to all,

have been experimenting with QV for some time now, mainly passively (changing existing Files and structures).

But I now have started a new project from scratch which really is fun. Here is a challenge for me, anyone who can help :

This is a relatively simple Pivot Table, per salesperson (1st Column) the sales are aggregated per year.

Now there is a commission system, which makes it interesting to see which salesperson (I only listed one here, but there are 1200) adds in a negative way to the total commission by a yearly negative change. So the yearly change is interesting to check / plot.

Using above / Blow / peek I have not been able to get any values in the last column (yearly change) so far.

Who can help me out here ?

[sunny_talwar]

You can try this

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

[neelamsaroha157]

You can use

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

[neelamsaroha157]

Check This

[johnw]

A problem with above() is that it's just looking at your chart, not at your data model. So what happens if I select 2014 and 2016, skipping 2015? Then my 2016 Yearly Change will be 2016 - 2014. Now for some sorts of analysis, that might be exactly what was intended by the Year selections. But for checking commissions? I doubt it. Or say I just want to see the yearly change for 2015. So I select 2015. Instead of seeing what I want, the yearly change goes away completely.

I think a better solution (though somewhat more complicated, and not without its own issues) would be to use an AsOf table so that the association between the current and previous year, and in the attached example even how the data is calculated, is part of the data model rather than relying on the chart layout and current selections. See attached. There are plenty of changes that can be made to fit your requirements better. I just don't know your exact requirements, such as "should I show a change for the first year a salesperson worked?" In the example I assumed the answer "yes", but it could just as easily be "no", which would require changes. Not that I'm planning to grill you on your exact requirements and solve them for you, just trying to say that even if the attached isn't what you're looking for, the idea would likely still be sound with some changes.

[#6]

many thanks for the quick and accurate replies

Dont know what happened on the initial tries with the above function, but the

Sum(Sales)-Above(Sum(Sales),1)

did the trick.

@John : thanx for the alternative asof option, will have a look at that the coming days.

For now I am trying to make a projected sales change, based on

Interval(YearEnd(Today()) - YearStart(Today()),'dd') /DayNumberOfYear(Today())

which then should only correct the acual year ... plowing my way through