Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Owning Record

Hi,

I am a novice with Qlikview  - have released a couple of QVD generator apps and design apps so bear with me here...

On a SalesPerson level I am trying to work out the monthly revenue drop.

I have no issues working this out on a Customer Level - the the complexity is that the Customer can move from 1 SalesPerson to another each Month.

Please see attached sample data and same output example =- if i can provide nmore info lpease let me know

thanks

Stuart

1 Solution

Accepted Solutions
Henric_Cronström
Not applicable

Re: Owning Record

What about

     If( Sum (Revenue)<> 0, Sum (Revenue)  - Before(Sum(total <Customer,Month> Revenue)))

Image3.png

HIC

6 Replies
Henric_Cronström
Not applicable

Re: Owning Record

If you use

     Sum (Revenue)

     Sum (Revenue) - Before(Sum(Revenue))

as expressions, you will get this:

Image2.png

HIC

Not applicable

Re: Owning Record

Thanks very much Henric, this works perfectly for the monthly changes.

Do you know how I can get this to also calculate the YTD change - the change from Dec 12 to Apr 03

Not applicable

Re: Owning Record

Actually no that doesnt work

if you look at the month of Feb 2014 you can see the customer moved from Sally to Dave. For Feb the movement should be 0.6 against Dave only. But we are seeing +5.87 on Dave and -5.29 on Sally.

So its just the net 0.6 that should show against Dave.

its the complexity of showing the net Customer movement against the Owning Salesperson at the time.


Henric_Cronström
Not applicable

Re: Owning Record

There are different solutions here, and most of them use Set Analysis. See for instance

Year-over-Year Comparisons

QlikView App: Simple Year To Date Example (Set Analysis)

Or search for ytd and yoy on the forum.

HIC

Henric_Cronström
Not applicable

Re: Owning Record

What about

     If( Sum (Revenue)<> 0, Sum (Revenue)  - Before(Sum(total <Customer,Month> Revenue)))

Image3.png

HIC

Not applicable

Re: Owning Record

thanks very much for your help again Henric - this works !