Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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

Re: set analysis, previous day. can't work.

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

7 Replies

Re: set analysis, previous day. can't work.

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

Not applicable

Re: set analysis, previous day. can't work.

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@

Not applicable

Re: set analysis, previous day. can't work.

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

Re: set analysis, previous day. can't work.

No problem

Not applicable

Re: set analysis, previous day. can't work.

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?

Re: set analysis, previous day. can't work.

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

Re: set analysis, previous day. can't work.

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

Community Browser