4 Replies Latest reply: Dec 4, 2011 3:56 PM by arnes beganovic RSS

    Latest date based on other field



      I have a pivot table (file Table1 in attachement) and I would like to calculate a latest price for every material in list. I am using set analysis and everything is OK until I ask for latest date. Then I get a problem. It will not calculate if there are few different prices. Table2 is the file with prices and other dimensions.


      Here are conditions:


      Sales_SoldToParty must be BPTQB

      Sales_SAPNumber must be 115111

      SalesDate must be latest date


      those three conditions will determin price for one material. It sounds easy but its not.


      I have tried: sum({<Sales_SoldToParty={'BPTQB'}, Sales_SAPNumber={115111}, SalesDate={$(=max(SalesDate))}>} SalesPrice) but it wont work.


      I have tried aggr(FirstSortedValue(SalesPrice, -SalesDate), VCCOHS) and this gives me latest prices but other conditions are not included. So I tried with set analysis and have done this: sum({<Sales_SoldToParty={'BPTQB'}, Sales_SAPNumber={115111}>} aggr(FirstSortedValue(SalesPrice, -SalesDate), VCCOHS)) but this works only if I have one row in database (Table2 in attachemnt). Is there any other solutions to include conditions in aggr function without set analysis?


      So, if someone has an idea how to summarize latest prices for whole sequences from one database and two other conditions, please help. Att the moment I am using private licence so I can not open other qlikview files. If you can write a formula or have idea here it will be great if you write it here.


      Thanks in advance.


      PS: This does not work either: if(aggr(Sales_SAPNumber,SalesVCC)=115111 and aggr(Sales_SoldToParty ,SalesVCC)='BPTQB', aggr(FirstSortedValue(SalesPrice, -SalesDate), VCCOHS))