Calculate Data with Range Function

[Viallijk]

Hello Everyone 

I Need to help Finished My Query in Load Editor 

In this Data I want to calculate Column Cogsacal By Id and date

My Formula Expression use 

if(RowNo()=1,Amount/[GR Qty],

(((above(if(RowNo()=1,[GR Qty]-SUM(InvoiceQuantity))))*above(TOTAL COGS))+(Amount))

/If(RowNo()=1,Sum([GR Qty]),RangeSum(Above( Sum([GR Qty]),1,RowNo()),-Sum([GR Qty]))))​

In Excel Formula is Using (Above Stock) * Above (Cogsa) + Amount / Stock

And I want to calculate Using Load Editor using Range And Peek

 
This My Formula in Load Editor 

My Expectation In column Cogsacal can get 894.432 in Colum Third and Colum 2 is 910.754 because is not have a Gr QTY

Please Help Me to resolved my problem in Qlik Sense 

Thank you

[marcus_sommer · Accepted solution]

There are some things which might be related to not resulting the expected values. The field-name in peek() isn't wrapped with single-quotes. In previous releases this could cause errors - nowadays I'm not sure if it accepted field-names without spaces or special chars in this way.

More relevant will be the order by statements within the loads with the interrecord-functions. In "Stock" it is only the date and in "Cogsacal" is it date and id - both statements needs to be checked. I could imagine that it should be rather:

order by Id, Date;

Means at first Id and then Date (by the last you may also consider the direction of asc or desc).

Further it's quite unusual to apply a cumulative logic with the range- and interrecord-functions without the use of any if-loops. In the most simple cases it are things like: if(rowno() = 1, ...) and/or if(Field = previous(Field), ...) and not seldom are nested if-loops needed to check the values of the current/previous records before applying any cumulative logic and/or branching in various versions. In some cases it could be a bit tricky but if you add a recno() and rowno() to these loads and also all the check- and cumulative logic in their parts to the load the behaviour/results could be easily checked within a table-box.

Beside this "Stock" is loaded with a where-condition and afterwards is a join applied - are you sure that this join worked like expected without changing the number of records?

- Marcus

[Viallijk]

Thank you Mr.Marcus to answer my problem.

i think not sure Mr Marcus because if  in load editor don't have a where condition number in data changing.

[marcus_sommer]

Sorry, I don't understand ...

[Viallijk]

Sorry, Mr Marcus For my response i mean to answear your Question 

Is Mean with a Join in stock many my data is changing the number of record

But thank you sir for help me to resolved my problem

-Vialli