Hello,
I have a list of materials and separate database with price change for those materials. One material can have one or more than one price change during its life. Let's take example:
Material: 176151 had 3 price changes:
2007-10-01 (88,37 eur)
2008-05-23 (89,98 eur)
2009-03-26 (85,93 eur)
Tables are connected by material.
Now, I would like to make expression that attach latest price to this material. I have tried few options but I failed:
sum({< Date = {'=max(Date)'}>} Price) adds all prices. I am getting 264,28 EUR. I think that the problem is in max(Date). Qlikview takes prices and creates sum by max date for every price. I guess that there is some kind of array and qlikview says: Ok I have 88,37 price, what is the max(Date) for it... And then it takes another price, and third one. Then it adds all prices by latest dates. Sum is not working. Not in format above. Count gives me resut: 3
Then I tried with FirstSortedValue function but I get the oldest price. I need oposite. Is there LastSortedValue function?
Then I tried with aggr function, but I can not see how to extrakt data from same table. I can make max(aggr(Price,Date)) and get max price, because qlikview creates table:
88,37 2007-10-01
89,98 2008-05-23
85,93 2009-03-26
I should get only one row: 85,93 2009-03-26 but there is no "rule" to make that expression. In this case latest price is the lowest one, but it is not general rule. I need to begin with dates, find greatest date, and to somehow extract price. So I need Date,Price table. Function aggr forces me to make Price, Date and to do expression with price, which is not possible in reality.
Is there some other way to solve this? When I click to material 176151 I would like to get price 85,93 in pivot table.
