Help with Aggr Sum of Qty

[davyqliks]

HI,

In my master table i have a qty field which is on multiple rows (repeated) due to another field fibre being different per row.

So i think i need to do a sum of the qty aggregated by the Order no to group and then divided by a count of the fibres in that order no.

this is my effort.

Aggr(Sum( [PO QTY])/ Count(Fibre),[Portal Order No.])

that gives the correct value per line but no total

and this:

(Aggr(Sum( [PO QTY]),[Portal Order No.]/ Count(Fibre)))

Gives an incorrect total which is repeated in line 1.

Is there anyone who can help me to achieve the correct qty per line and as a total?

thank you in advance.

regards,

Daniel

[Taoufiq_Zarra]

@davyqliks  can you share a sample data ?

[davyqliks]

HI,

Apologies but there is some sensitive brand information in the model.

Thank you for the reply,

i have managed to correct in the load script with a similar sum grouped by the portal order no.

POQTY:
Load
"Portal Order No.",

Sum( [PO QTY])/ Count(Fibre) as POQTY
Resident Master
group by "Portal Order No."
;

this has given the desired result

Thank you for your willing help

Daniel

[rupalimane1390]

@davyqliks  To aggregate by order number, here is the expression :

sum(aggr((Sum( [PO QTY]/Count(Fibre) ),[Portal Order No.]))

Aggregation function example in qlik as below :

Sum ( aggr ( sum ( Sale ), Company_Name ) ) //sum of sales aggregated or grouped by Company_Name