Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@davyqliks can you share a sample data ?
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
@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