Display only the most recent records in pre-built analysis "Inventory - Onhand Balance"

Jonathan Chan gmg

Is there a way to modify the pre-built analysis "Inventory - Onhand Balance" so it only outputs the most recent values for each SKU on both "Costed Onhand Amount" and "Costed Onhand Quantity" attributes instead of values aggregated since day 1?





I have tried the following methods but they resulted either in showing aggregated values or error:


1) Aggregated values shown instead:


• "Start Date" dimension formula:

MAX("- Inventory Valuation Details - Onhand"."As of Date")





• "Aggregate Rule (Total Row)" set to "None" for both "Costed Onhand Amount" dimension and "Costed Onhand Quantity" dimension, per enclosed screenshot.





2) Error:


• "Costed Onhand Amount" dimension formula: 


FILTER("- Inventory Valuation - Onhand"."Costed Onhand Amount" USING (MAX("- Inventory Valuation Details - Onhand"."As of Date")))





• Error code:


Formula syntax is invalid.


[nQSError: 10058] A general error has occurred. Please have your System Administrator look at the log for more details on this error. (HY000)

[nQSError: 43113] Message returned from OBIS. Please have your System Administrator look at the log for more details on this error. (HY000)

[nQSError: 27002] Near <)>: Syntax error Please have your System Administrator look at the log for more details on this error. (HY000)

[nQSError: 26012] . Please have your System Administrator look at the log for more details on this error. (HY000)

SQL Issued: SELECT FILTER("- Inventory Valuation - Onhand"."Costed Onhand Amount" USING (MAX("- Inventory Valuation Details - Onhand"."As of Date") )) FROM "Costing - Inventory Valuation Real Time"

Thank you.