Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

set analysis, previous day. can't work.

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.

1 Solution

Accepted Solutions
Highlighted

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')

View solution in original post

7 Replies
Highlighted

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')

View solution in original post

Highlighted
Not applicable

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@

Highlighted
Not applicable

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

Highlighted

No problem

Highlighted
Not applicable

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?

Highlighted

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')

Highlighted

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