set analysis, previous day. can't work.

[#1]

NUM(Sum({<Inventory_Date={'04/01/2016'}, QUANTITY_UM={"CTN"}>}sum_on_hand_qty/CONVERSION_QTY), '###,###.00')

The above works. But when I tried

NUM(Sum({<Inventory_Date={"$(=DATE(MAX(Inventory_Date)))"}, QUANTITY_UM={"CTN"}>}sum_on_hand_qty/CONVERSION_QTY), '###,###.00')

just so it can retrieve the latest inventory_date, it doesn't work. it just gives me a value of zero. Any ideas? I have been trying this for sometime.

[sunny_talwar · Accepted solution]

Does this -> =DATE(MAX(Inventory_Date)) in a text box give you 04/01/2016 in the same format? If format doesn't work, try this:

=Num(Sum({<Inventory_Date={"$(=Date(Max(Inventory_Date), 'MM/DD/YYYY'))"}, QUANTITY_UM={"CTN"}>}sum_on_hand_qty/CONVERSION_QTY), '###,###.00')

[#3]

my god...it works. But...I purposely display this "Date(Max(Inventory_Date))", but it is showing exactly same format as the data. Thanks Sunny@

[#4]

for mine, it was 'DD/MM/YYYY' though.

[sunny_talwar]

No problem

[#6]

Sunny,

What if I need to compare by MonthName?

NUM(   Sum( {$<QUANTITY_UM={"CTN"},Inventory_Date={"$(=DATE(MAX(Inventory_Date), 'DD/MM/YYYY'))"}, dateSet={"$(=MonthName(MAX(dateSet)))"}>}[Ordered Qty]/CONVERSION_QTY),

         '###,###.00'

   )

like this?

[sunny_talwar]

Do you have a field in the script created using MonthName? If you do, then may be this:

=Num(Sum({$<QUANTITY_UM={"CTN"}, MonthYear={"$(=MonthName(Max(Inventory_Date)))"}, dateSet={"$(=MonthName(MAX(dateSet)))"}>}[Ordered Qty]/CONVERSION_QTY), '###,###.00')

if not, then may be this:

=Num(Sum({$<QUANTITY_UM={"CTN"},Inventory_Date={"$(='>='& Date(MonthStart(Max(Inventory_Date)), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(Inventory_Date)), 'DD/MM/YYYY'))"}, dateSet={"$(=MonthName(MAX(dateSet)))"}>}[Ordered Qty]/CONVERSION_QTY), '###,###.00')

[sunny_talwar]

Actually I see you added dateSet, how is dateSet created in the script?