Condition where latest date is before production date

[#1]

Hello again

Here we have a discussion that has solved one great problem

http://community.qlik.com/message/170168#170168

Now, I can summarise latest prices for all materials in one sequence and I can get latest value for that sequence. There is one more condition I am fighting with all day long, and it seems that I have no enough knowledge for it. I need some help with syntax.

In connection to sequences, I have a field with dates when one seqence is produced. So, there is load statment that loads a long list with two columns:

[Sequence Number] and Datum (this is field name for Date)

QUestion: How can i calculate same thing (sum of latest prices) but with condition where latest date is before sequence production. So, If I have sequence production date "2011-02-01" I would like to ignore all prices after that day, so that FirstSortedValue finds only latest date before named date. I have tried with different formulas, variables etc but with no sucess.

Here is working formula for latest prices:

=sum(aggr(FirstSortedValue({<Sales_SAPNumber={'115111'}, Sales_SoldToParty={'BPTQB'}>} Distinct SalesPrice, -SalesDate), VCCOHS, [Sequence Number] ))

If I create condition  SalesDate={'<2011-01-01'} it works, but my date depends on sequence production date, and it should be dynamic. Every sequence has different date and if I put them in one pivot table I need to get total based on latest prices before production date for every sequence. If I use field name Datum it will not work. I think that main reason is that it does not recognize sequence number in FirstSortedValue. I am attaching a simple qlikview file here. If you go to Sequence Info sheet, you will find pivot table and the expression. You can also see that sequences have different dates during time but total value is same. That is impossible in reality.

How to use file: Click att one PML code and you will get a list of all sequences with same material list in it. Those sequences has same total value over time, and the value should be different.

Thanks in advance

[Miguel_Angel_Baeyens]

Hi,

So you want the max value for SalesDate to be less than or equal to the Datum field, if I got you right. Then, the following should work

Sum({< Sales_SAPNumber = {'115111'}, Sales_SoldToParty = {'BPTQB'} >} 
  If(Aggr(NODISTINCT Max(SalesDate), [Sequence Number], VCCOHS) <= Datum, SalesPrice)) 

I'm not very familiar with your data model, and although I've read your previous post it's not cristal clear.

Hope that helps anyway.

Miguel

[#3]

Hello Miguel Angel Baeyens,

Your function gives price if production date (Datum) is bigger then latest price date. Your if condition works only if max(SalesDate)<Datum. If i had price changes after production date, this function returns false, and in this case it is 0.

[#4]

Well, in bottom line we have this in my database for one material:

SalesDate, Price, Sales_SoldToParty, Sales_SAPNumber

2011-04-12; 35,86; BPTQB, 115111

2011-04-19; 36,13; BPTQB; 115111

2011-05-28; 100; BPTQB; 115111

Now, we have one sequence (one car seat) that is produced on 2011-05-27. This seat has 50 parts and extract from database above is extract for one of them. So one material came in production on 2011-04-12 with price 35,86. Then, on 2011-04-19 we have had changed the price to 36,13, and I added virtual row (to see the diff) with assumption that price has changed again on 2011-05-28 to 100. So, this last price change is one day after production. Lets assume that we produce again same seat with same materials, but now on 2011-05-30. Both seats have same production code so when I select this code (in separate listbox) those two sequences will be showed in pivot table.

Pivot table has those two seats in one dimension, and material in second dimension. I am writing expression that will give me total value based on latest price dates but before production date. So, if we assume that both seats have 50 materials and that first sequence has total value (sum of all prices) till exempel 1000, and if we know that one day after production one material has changed its price... So I need to see this difference with next seat.

If price has jumped from 36,13 to 100 it means that seats that is produces after 2011-05-28 (or on that day) will have 100-36,13=63,87 EUR higher total value than seat that has produces on 2011-05-27.

So, if I can suggest "pseudo solution" it would be like this:

Seat 1 (produced on 2011-05-27) needs to have this in some aggr function:

SalesDate, Price, Sales_SoldToParty, Sales_SAPNumber

2011-04-12; 35,86; BPTQB, 115111

2011-04-19; 36,13; BPTQB; 115111

Then it will search with FirstSortedValue (with minus sign, because I need latest price) and return 36,13 EUR because latest date here is 2011-04-19

Seat 2 (produced on 2011-05-30) needs to have this is some aggr function:

SalesDate, Price, Sales_SoldToParty, Sales_SAPNumber

2011-04-12; 35,86; BPTQB, 115111

2011-04-19; 36,13; BPTQB; 115111

2011-05-28; 100; BPTQB; 115111

Then it will search with FirstSortedValue (with minus sign, because I need latest price) and return 100 EUR because latest date here is 2011-05-27

Sound easy to me, but...

ps: if I would like to do this in excel I could use vlookup to find correct price. Dates in colA, prices in colB and formula: =VLOOKUP(D2;A:B;2;1) where D2 has production date. Key factor in formula is number 1 att the end because it is looking for first smaller value it there is no exact value.

[#5]

Hello again,

i have solved the problem with latest prices before production date for materials. Now I have a list with correct prices, correct, dates for materials. My formula is:

=if(sum({<Sales_SAPNumber={'115111'}, Sales_SoldToParty={'BPTQB'}>} aggr(if(Datum>SalesDate,1,0),VolvoPO,VCCOHS,[Sequence Number]))=1,

    sum( DIStinct SalesPrice),

        if(max(SalesDate)<Datum,

            FirstSortedValue(SalesPrice,-aggr(SalesDate,VolvoPO,VCCOHS,[Sequence Number])),

                FirstSortedValue(SalesPrice,-aggr(if(Datum>SalesDate,SalesDate),VolvoPO,VCCOHS,[Sequence Number]))

                ))

Now I have a problem with Total in pivot table. Pivot table does not calculate total because I have 2 Firstsortedvalue functions as part of my if statement. Function is splitted into two parts:

1) If there is only one price in database then give me that price. The trick is in counting in aggr function.

2) If there is more than one price in database then look if latest date for material is before production date. If yes, then use firstsortedvalue function and give me price with that latest date. If no, then it is third part of if statement

3)If "no" in point 2, then combine aggr and if to exclude all dates after production date and then use FirstSortedValue again to find latest price in filtered list.

Well, this is my solution. Now, I can delete total from pivot table and live happily ever after with condition that I need export table in excel and create sum manually, or to use some trick in pivote table which will give me total.

Maybe I can hope that programers will create in next version an option to ignore formulas in total, and just summarize values that formula gives