Announcements
cancel
Showing results for
Did you mean:
Creator II

## need logic help to calculate upper 10 rows average with a condition.

Hi,

In sample data i have transit loss. in some rows transit loss value is blank. I want average transit loss of previous ten rows  in blank cell of transit loss field, with condition only previous 10 row with data will be consider .

thanks

Lalit Kumar

Labels (1)
• ### New to QlikView

4 Replies
MVP
What is the expected output needs to look like?
Creator II
Author

in attached qvw expected result at ROWNO 11 is ok, result at ROWNO 36 is also ok, but result at ROWNO 45 is wrong.

at ROWNO 45 result should be average of Tranistloss ROWNO 44 to 37 and 35 to 34 ie.--0.0190121007571395

same condition will be applied in rest blank cells. only cell with value will be considered.

MVP

There might be a better way to do this... but this seems to work

```Test:
RR_Weight,
Gross_Weight,
Tare_Weight,
Gross_Weight - Tare_Weight as ActualWeight,
(RR_Weight - (Gross_Weight - Tare_Weight))/RR_Weight as Transitloss
//     (RR_Weight - (Gross_Weight - Tare_Weight))/RR_Weight as TransitLoss_Temp
FROM Data.xlsx
(ooxml, embedded labels, table is Sheet1);

Test1:
RR_Weight,
Gross_Weight,
Tare_Weight,
ActualWeight,
Transitloss,
RowNo() AS ROWNO
Resident Test

DROP Table Test;

For i = 0 to 100

Left Join (Test1)
LOAD ROWNO + \$(i) as ROWNO,
Transitloss as Transitloss_\$(i)
Resident Test1;

NEXT

CrossTableTest:
Resident Test1;

DROP Table Test1;

FinalTable:
RR_Weight,
Gross_Weight,
Tare_Weight,
ActualWeight,
Transitloss,
ROWNO,
Avg(Value) as TransitLoss_Temp
RR_Weight,
Gross_Weight,
Tare_Weight,
ActualWeight,
Transitloss,
ROWNO,