topic peek() error in recursive sum in QlikView

peek() error in recursive sum

Thu, 10 Mar 2016 10:16:06 GMT

Hello!

Could someone tell me, please, what can be the problem: I'm counting a recursive sum of sales by products and dates. The goal-to have sum of sales from the beginnig of the month for each day.

The problem is that on the second cycle peek('Sales') returns NULL =(

Table Products-array of productNames

Why& how to fix it?

Thanks in advance)

Even if I do it in one load in two for-cycles, the error is the same

for vRow = 1 to NoOfRows('Products')
let MyVar = peek('ProductID', vRow - 1, 'Products');
'LastQuaterSales_Table:
load
Date,
ProductID,
sum(Sales) as SalesPerDay,
sum(SalesQty)as SalesPerDayQty
Resident LastQuaterSales where ProductID '$(MyVar)' Group by ProductID,Date;

for dateLoc=MakeDate(2014,4,1) to MakeDate(2014,6,30)     step 1
let dd=date(dateLoc);
vSale=0;
vSaleQty=0;

IF (dateLoc=MakeDate(2014,4,1)) then

vSale= numsum(lookup('LastQuaterSales', 'ProductID', '$(MyVar)', 'LastQuaterSales_Table'),Lookup('SalesPerDay','Date', '$(dd)','SalesPerDay_Table'));
vSaleQty=numsum(lookup('LastQuaterSalesQty', 'ProductID','$(MyVar)', 'LastQuaterSales_Table'),Lookup('SalesPerDayQty','Date', '$(dd)','SalesPerDay_Table'));
Sales:
load * inline ['ProductID', Date, Sales, SalesQty
'$(MyVar)', $(dd), $(vSale), $(vSaleQty)
];

ELSE

vSale=floor(numsum(Lookup('SalesPerDay','Date', '$(dd)','SalesPerDay_Table'), peek('Sales')));
vSaleQty=numsum(Lookup('SalesPerDayQty','Date', '$(dd)','SalesPerDay_Table'),peek('SalesQty'));

Sales:
load * inline ['ProductID', Date, Sales, SalesQty
'$(MyVar)', $(dd), $(vSale), $(vSaleQty)
];
ENDIF
next
next

////////////////////---------------------------------------------

for dateLoc=MakeDate(2014,4,1) to MakeDate(2014,6,30) step 1

let dd=date(dateLoc);

Sales:
load
'$(MyVar)' as ProductID,
'$(dd)' as Date,
if('$(dd)'=MakeDate(2014,4,1),
numsum(lookup('LastQuaterSales', 'ProductID', '$(MyVar)', 'LastQuaterSales_Table'),Lookup('SalesPerDay','Date', '$(dd)','SalesPerDay_Table')),
numsum(Lookup('SalesPerDay','Date', '$(dd)','SalesPerDay_Table'),peek('Sales')))
) as Sales,
if('$(dd)'=MakeDate(2014,4,1),
numsum(lookup('LastQuaterSalesQty', 'ProductID','$(MyVar)', 'LastQuaterSales_Table'),Lookup('SalesPerDayQty','Date', '$(dd)','SalesPerDay_Table')),
floor(numsum(Lookup('SalesPerDay','Date', '$(dd)','SalesPerDay_Table'), peek('SalesQty')))) as SalesQty
resident Test where ProductID='$(MyVar)';
next
DROP table SalesPerDay_Table;

next

Сообщение отредактировано: Alexandra B

Re: peek() error in FOR

Gysbert_Wassenaar — Thu, 10 Mar 2016 11:39:33 GMT

The goal-to have sum of sales from the beginning of the month for each day.

In that case try this:

Temp:

load
Date,

     Date(MonthStart(Date),'YYYYMM') as Month,
     ProductID,
     sum(Sales) as SalesPerDay,
     sum(SalesQty)as SalesPerDayQty
Resident

LastQuaterSales

Group By

Date, ProductID

;

Result:

load
Date,

     Month,
     ProductID,
     SalesPerDay,
     SalesPerDayQty,

If(Month = Previous(Month) and ProductID = Previous(ProductID),

Rangesum(Peek(CumSalesPerDay),SalesPerDay),

SalesPerDay) as CumSalesPerDay,

If(Month = Previous(Month) and ProductID = Previous(ProductID),

Rangesum(Peek(CumSalesPerDayQty),SalesPerDayQty),

SalesPerDayQty) as CumSalesPerDayQty

Resident

Temp

Order By

ProductID,

Month

;

DROP Table Temp;

Re: peek() error in recursive sum

Thu, 10 Mar 2016 12:22:55 GMT

Thanks for your response! I made a mistake, I need aggregation from the beginnig of the year=(

And the fact is, last quater table has already aggregated data for the last day of quater (not for all products), and also i have a table of curent quater for some days (not all days and not for all products, only where sales were). And result must have all dates for all products (sales can be positive and negative-we sell and we buy)..

Re: peek() error in recursive sum

Gysbert_Wassenaar — Thu, 10 Mar 2016 12:32:18 GMT

No problem. Replace Month and MonthStart with Year and YearStart.