topic Re. :Re: Re. :Counting sales over period in load script. in QlikView

Counting sales over period in load script.

Wed, 05 May 2010 11:59:16 GMT

Hi to everyone.
I have two tables:

Sales:LOAD date(Date) as Date, Product, Sales INLINE [ Date, Product, Sales 01.04.2010, Prod1, "297,77" 01.04.2010, Prod2, "889,36" 01.04.2010, Prod3, "313,52" 02.04.2010, Prod1, "126,18" 02.04.2010, Prod2, "656,22" 02.04.2010, Prod3, "624,99" 03.04.2010, Prod1, "823,88" 03.04.2010, Prod2, "392,52" 03.04.2010, Prod3, "561,32" 04.04.2010, Prod1, "265,35" 04.04.2010, Prod2, "924,12" 04.04.2010, Prod3, "990,04" 05.04.2010, Prod1, "254,63" 05.04.2010, Prod2, "106,09" 05.04.2010, Prod3, "843,05" 06.04.2010, Prod1, "657,90" 06.04.2010, Prod2, "309,20" 06.04.2010, Prod3, "524,59" 07.04.2010, Prod1, "549,02" 07.04.2010, Prod2, "866,21" 07.04.2010, Prod3, "738,24"];Products:LOAD * INLINE [ Product, Period Prod1, 3 Prod2, 4 Prod3, 5];

I need to get in load script a table that will have a counted sum of sales for this product over the period that is defined for ecery product.
For example - I'll have in a row:
Date=01.04.2010; Product=Prod1; SalesOverPeriod=297,77 that is: 0+0+297,77
Date=06.04.2010; Product=Prod2; SalesOverPeriod=1731,93 that is: 392,52+924,12+106,09+309,20

Full table must look like:

SalesOverPeriiod:LOAD * INLINE [ Date, Product, SalesOverPeriod 01.04.2010, Prod1, "297,77" 01.04.2010, Prod2, "889,36" 01.04.2010, Prod3, "313,52" 02.04.2010, Prod1, "423,95" 02.04.2010, Prod2, "1545,58" 02.04.2010, Prod3, "938,51" 03.04.2010, Prod1, "1247,83" 03.04.2010, Prod2, "1938,10" 03.04.2010, Prod3, "1499,83" 04.04.2010, Prod1, "1215,41" 04.04.2010, Prod2, "2862,22" 04.04.2010, Prod3, "2489,87" 05.04.2010, Prod1, "1343,86" 05.04.2010, Prod2, "2078,95" 05.04.2010, Prod3, "3332,92" 06.04.2010, Prod1, "1177,88" 06.04.2010, Prod2, "1731,93" 06.04.2010, Prod3, "3543,99" 07.04.2010, Prod1, "1461,55" 07.04.2010, Prod2, "2205,62" 07.04.2010, Prod3, "3657,24"];

Re. :Counting sales over period in load script.

martin59 — Wed, 05 May 2010 12:15:53 GMT

Hello,

Why don't you accumulate your values in a graphic object ?

You can enter this expression :

sum(sales)

check this option :

With Products and Date as dimensions

Martin

Re. :Counting sales over period in load script.

Wed, 05 May 2010 12:21:14 GMT

Because I need to work with this table doing some more calculations, and because in my real table there are more than 300 dates, something like 2k products and every product has a different period for counting - starting from 7 to 120. And one more - for every date and product I need to have their uniqe SalesOverPeriod sum.

Re. :Re: Re. :Counting sales over period in load script.

martin59 — Wed, 05 May 2010 12:40:37 GMT

So you can do this :


tmpSales:
LOAD date#(Date,'DD.MM.YYYY') as Date,
 Product,
 Sales
INLINE [
 Date, Product, Sales
 01.04.2010, Prod1, "297,77"
 01.04.2010, Prod2, "889,36"
 01.04.2010, Prod3, "313,52"
 02.04.2010, Prod1, "126,18"
 02.04.2010, Prod2, "656,22"
 02.04.2010, Prod3, "624,99"
 03.04.2010, Prod1, "823,88"
 03.04.2010, Prod2, "392,52"
 03.04.2010, Prod3, "561,32"
 04.04.2010, Prod1, "265,35"
 04.04.2010, Prod2, "924,12"
 04.04.2010, Prod3, "990,04"
 05.04.2010, Prod1, "254,63"
 05.04.2010, Prod2, "106,09"
 05.04.2010, Prod3, "843,05"
 06.04.2010, Prod1, "657,90"
 06.04.2010, Prod2, "309,20"
 06.04.2010, Prod3, "524,59"
 07.04.2010, Prod1, "549,02"
 07.04.2010, Prod2, "866,21"
 07.04.2010, Prod3, "738,24"
];

Products:
LOAD * INLINE [
 Product, Period
 Prod1, 3
 Prod2, 4
 Prod3, 5
];

Sales:
LOAD Product,
 Date,
 Sales,
 if(Product=peek('Product'),peek('SalesOverPeriod')+Sales,Sales) as SalesOverPeriod;
LOAD *,
 0 as SalesOverPeriod
RESIDENT tmpSales
ORDER BY Product, Date;

DROP TABLE tmpSales;

Re. :Re: Re. :Counting sales over period in load script.

Wed, 05 May 2010 12:57:06 GMT

Umh thanks but... In this table you make a full accumulation, and I don't need full accumulation, I need an accumulation over the defined period - as in table "Products" - for Prod1 - 3 last days, for Prod2 - 4 last days, for Prod3 - 5 last days etc.

Re. :Re: Re. :Counting sales over period in load script.

Wed, 05 May 2010 13:38:46 GMT

Using your idea got the right script:

tmpSales:LOAD date#(Date,'DD.MM.YYYY') as Date, Product, SalesINLINE [ Date, Product, Sales 01.04.2010, Prod1, "297,77" 01.04.2010, Prod2, "889,36" 01.04.2010, Prod3, "313,52" 02.04.2010, Prod1, "126,18" 02.04.2010, Prod2, "656,22" 02.04.2010, Prod3, "624,99" 03.04.2010, Prod1, "823,88" 03.04.2010, Prod2, "392,52" 03.04.2010, Prod3, "561,32" 04.04.2010, Prod1, "265,35" 04.04.2010, Prod2, "924,12" 04.04.2010, Prod3, "990,04" 05.04.2010, Prod1, "254,63" 05.04.2010, Prod2, "106,09" 05.04.2010, Prod3, "843,05" 06.04.2010, Prod1, "657,90" 06.04.2010, Prod2, "309,20" 06.04.2010, Prod3, "524,59" 07.04.2010, Prod1, "549,02" 07.04.2010, Prod2, "866,21" 07.04.2010, Prod3, "738,24"];Products:LOAD * INLINE [ Product, Period Prod1, 3 Prod2, 4 Prod3, 5];Left join (tmpSales) load * resident Products;Sales:LOAD Product, Date, Sales, if(Product=peek('Product'),peek('SalesOverPeriod')+Sales-if(Product=peek('Product',-Period),peek('Sales',-Period),0),Sales) as SalesOverPeriod;LOAD *, 0 as SalesOverPeriodRESIDENT tmpSalesORDER BY Product, Date;DROP TABLE tmpSales;

thnx, Martin.