Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So I have an issue with a set analysis formula that I cannot figure out what is wrong with it. I am trying to have the formula display the value of (AFPO.WEWRT/AFPO.PSMNG) where the ProductionStartMonth (formatted as a date MM-YYYY) is the maximum which would display the newest price per unit.
The formula I came up with is MAX({<ProductionStartMonth=MAX(ProductionStartMonth)>} (AFPO.WEWRT/AFPO.PSMNG)) but that isnt working. The error message starting after the }. What am I missing with this?
If the date could be causing the issue I could change it to the number AFPO.AUFNR but I would still need the max of that value
Hi, mainly the syntax for se analysis needs the values between brackets, also to evaluate max value inside brackets $-expansion can be used:
MAX({<ProductionStartMonth={$(=MAX(ProductionStartMonth))}>} (AFPO.WEWRT/AFPO.PSMNG))
Maybe you need to set date format:
MAX({<ProductionStartMonth={$(=Date(MAX(ProductionStartMonth), MM-YYYY))}>} (AFPO.WEWRT/AFPO.PSMNG))
This can work if WEWRT and PSMNG only has one value per ProducctionStartMonth, if there can be many values you have to aggregate them:
Sum({<ProductionStartMonth={$(=Date(MAX(ProductionStartMonth), MM-YYYY))}>} AFPO.WEWRT) /Sum({<ProductionStartMonth={$(=Date(MAX(ProductionStartMonth), MM-YYYY))}>}AFPO.PSMNG)
Ive tried your formulas and I cant get them to work either. I have reconfigured my load script to give me a Production Year value (ProductionYear) and can get close to the value I want by using the function:
AVG({<ProductionYear={2019}>} (AFPO.WEWRT/AFPO.PSMNG))
This would return the average price per unit for all 2019 production orders. It's close to what I am wanting but I would prefer for it to display the value of the most recent Production order. My load script for the production day is: date(monthstart(AFPO.STRMP), 'MM-DD-YYYY') as ProductionStartDay,
date(monthstart(AFPO.STRMP), 'MM-YYYY') as ProductionStartMonth,
date(monthstart(AFPO.STRMP), 'YYYY') as ProductionYear,
Something is not working right in the section for the max date selection. I am lost as to why as it seems like it should work.
Can you try this
Max({<ProductionStartMonth = {"$(=Date(Max(ProductionStartMonth), 'MM-YYYY'))"}>} AFPO.WEWRT/AFPO.PSMNG)
Returned a value for only 1 line out of the 300 or so.
Okay so what is the issue? May be you only have one row for Max ProductionStartMonth....
There is approximately 300 parts that have a production date therefore they should all have a production max date.
I am not sure I understand... are you able to share some sample data and the output you expect to see from it?
See data table above. Trying to add column that displays the price per part on the most recent production order. All parts shown that have a production price should have a max production date that would be the most recent production order.