
Re: Condition where latest date is before production date
Miguel Angel Baeyens de Arce Dec 5, 2011 7:14 PM (in response to arnes beganovic)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

Re: Condition where latest date is before production date
arnes beganovic Dec 6, 2011 6:57 AM (in response to Miguel Angel Baeyens de Arce )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.

Re: Condition where latest date is before production date
arnes beganovic Dec 6, 2011 7:54 AM (in response to arnes beganovic)Well, in bottom line we have this in my database for one material:
SalesDate, Price, Sales_SoldToParty, Sales_SAPNumber
20110412; 35,86; BPTQB, 115111
20110419; 36,13; BPTQB; 115111
20110528; 100; BPTQB; 115111
Now, we have one sequence (one car seat) that is produced on 20110527. This seat has 50 parts and extract from database above is extract for one of them. So one material came in production on 20110412 with price 35,86. Then, on 20110419 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 20110528 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 20110530. 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 20110528 (or on that day) will have 10036,13=63,87 EUR higher total value than seat that has produces on 20110527.
So, if I can suggest "pseudo solution" it would be like this:
Seat 1 (produced on 20110527) needs to have this in some aggr function:
SalesDate, Price, Sales_SoldToParty, Sales_SAPNumber
20110412; 35,86; BPTQB, 115111
20110419; 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 20110419
Seat 2 (produced on 20110530) needs to have this is some aggr function:
SalesDate, Price, Sales_SoldToParty, Sales_SAPNumber
20110412; 35,86; BPTQB, 115111
20110419; 36,13; BPTQB; 115111
20110528; 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 20110527
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.

Condition where latest date is before production date
arnes beganovic Dec 8, 2011 4:47 AM (in response to arnes beganovic)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


