peek() error in recursive sum

[#1]

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 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);

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

[Gysbert_Wassenaar]

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;

[#3]

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)..

[Gysbert_Wassenaar]

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