Finding differences when match?

[#1]

How do I calculate, either in script or  in a table the difference in Sales where there is a matching "dimensions"?

E.g.

CustomerNr

ArtNr

Year (or another period)

and Sales.

When CustomerNr & ArtNr match between different time periods how do I calculate the difference?

CustMtrl	Year	Sales
5436698|3154	2011	317000
5436698|3154	2010	325000
5436698|3169	2011	100000
5436698|3169	2010	150000

[#2]

If you are looking for the difference in Sales by Year then you can:

• create Pivot Table
• Add 3 dimensions Customer, Art Nr and Year
• Add expression Sum(Sales)
• Drag the Year column on top of the Sales column, then each year shows as a separate column
• Add an expression Sum(Sales) - Before(Sum(Sales))

The result will show you how the amounts have changed by year.

Ingo.

[#3]

In the script one way you could handle this is utilizing the peek function:

Peek looks at the next row entry in the data.

I made an inline table demonstrating this(without more specificity I don't know quite what you are looking for):

test:

LOAD * INLINE [

CUSTOMERNBR, ARTNBR, YEAR, SALES

5436698, 3154, 2011, 317000

5436698, 3154, 2010, 325000

5436698, 3169, 2011, 100000

5436698, 3169, 2010, 150000

]

;

test2:

LOAD *,

IF (CUSTOMERNBR = peek(CUSTOMERNBR) and ARTNBR = peek(ARTNBR), fabs( SALES - peek(SALES) ),0) as  DIFFERENCE

Resident test;

drop table test;

Hope this helps!

[#4]

Getting closer I think. But not solved yet.

The sales amount is actually many orderlines. And I would like to Sum() those, but can I do that in the loading script and get a valid number?

Also to complicate things much more I would like to be able to use the Qlikview Components (open source scripts) calendar functions. Because I would like to calculate for Price/Volume effect for Month/vSetYTD etc.

[CELAMBARASAN]

Hi,

     Use group by to do that.

     SourceTable:

     Load

          CUSTOMERNBR&'_'&ARTNBR CustomerArt

          CUSTOMERNBR,

          ARTNBR,

          Year,

          Sum(Sales) as Sales

     From DataSource Group by CUSTOMERNBR,ARTNBR,Year;

     Output:

     Load

           *,

           if(Previous(CustomerArt)=Previous(CustomerArt),fabs(Previous(Sales)-Previous(Sales)),0) as Sales Diff

     Resident SourceTable;

Celambarasan