Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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
sunny_talwar

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

Not applicable
Author

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
Author

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

sunny_talwar

No problem

Not applicable
Author

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?