topic Finding differences when match? in QlikView

Finding differences when match?

Fri, 09 Mar 2012 14:19:11 GMT

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

Finding differences when match?

Fri, 09 Mar 2012 17:19:52 GMT

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.

Finding differences when match?

Fri, 09 Mar 2012 17:46:53 GMT

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!

Finding differences when match?

Mon, 12 Mar 2012 15:22:50 GMT

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.

Finding differences when match?

CELAMBARASAN — Tue, 13 Mar 2012 04:58:49 GMT

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