Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am having a situation and i just can't find an answer.
I made a pivot table. Rows are "Branches" and columns are "weeks". Each branch can have multiple shops.
The idea is to show the IdShop with the max sales in the week. So Max(aggr(Sum(Sales), week, branch, IdShop) shows the right sales numbers. But i need to show the IdShop to which each calculated Max Sum Sales belong. How do i return the value of the IdShop, that answers the Max(aggr(Sum(Sales), week, branch, IdShop) criteria? Tried to do it with the IF statement ( If (Max(aggr(Sum(Sales), week, branch, IdShop), IdShop), but it shows only one value of single IdShop in pivot table. Hopefully you can understand what i just wrote. I would love to attach the qwv file, but it is all in Russian though.
Thank you.
Privet Alex, kak dela?
not sure I totally understand your question, but how about this expression:
FirstSortedValue(IdShop, -aggr(sum(Sales),branch,week,IdShop))
you can get some ideas here if it does not work in your case
QlikView Technical Brief - AGGR.docx
Boris