Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

Yearly change in sales

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 :

2017-06-08_15h10_27.png

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 ?

5 Replies
Highlighted

Re: Yearly change in sales

You can try this

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

Highlighted
neelamsaroha157
Valued Contributor II

Re: Yearly change in sales

You can use

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

Highlighted
neelamsaroha157
Valued Contributor II

Re: Yearly change in sales

Check This

Highlighted
MVP
MVP

Re: Yearly change in sales

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.

Highlighted
Not applicable

Re: Yearly change in sales

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