Qlikview Query to identify data into the same table
I'm building a model to improve perfomance on an area of my company to find inefficiencies on the purchasing behaviour of Procument. But right now I have some problems. I'm kind of new in Qlikview, so far I have being able to almost finish the project, but this part it's getting me crazy. I hope I can explain it with some pictures, because I don't have any code to achive that, I even tried splitting the data on different tables but I still don't get it.
I have a set of data which must be complemented depending on the data itself.
In the picture you can see the data base. What I want to do is to fill PPM (maked in yellow). The name of the rows are the ones marked in grey.
The logic behind this, is to review the purchases of my procurement team, where I calculate the inefficiency and missing opportunities based on price.
The rules I use are, if you bought in your actual month at a higher price than the "Lowest Price", you have an inefficiency.
The Lowest Price is defined by if you buy at 1,916 from supplier 00000001086, but in a frame of time of 3 month you have bought to any other supplier at a lower price, you have an inefficiency.
As far I already have identify the lowest price for each supplier. The problem is I must find the Period when this price was used.
In the case I show in the picture, the lowest price is 1,781 wich was bought to supplier 000001118 on January19.
How could I writte down a code to fill the PPM(Lowest Price) column with the period as the example showed?
Sorry if my english is not so good, not my native language. If you need more info please let me know or if I must change something.