Latest date based on other field

[#1]

Hello,

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))

[#2]

Somebody?

[rwunderlich]

It may be helpful if you posted a sample qvw.

[swuehl · Accepted solution]

Hi,

I assumed that your fields VCCOHS and SalesVCC are associated so I renamed VCCOHS to SalesVCC in my script to get the tables linked.

Then, if you create a table chart with dimension SalesVCC, you could use this as expression:

=FirstSortedValue({<Sales_SAPNumber={'115111'}, Sales_SoldToParty={'BPTQB'}>} SalesPrice, -SalesDate)

Very similar to your one approach, just added the set expression to limit on SAP# and SoldToParty.

Hope this helps,

Stefan

[#5]

Bingo

I did not know that I can put conditions in FirstSortedValue function. Thanks a lot. That works perfectly