Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All
I wrote this set analysis to calculate Qty of Expired Stock on hand
Sum({$<Expiry_Date={"<=$(=Today())"}>}SOH)
But It show 0 as the result, can you help to correct my set analysis so i can get the correct result
Regards
Sofyan B
Hi Sofyan, syntax seems right, you can create a straight table with Expiry_Date as dimension and Sum(SOH) as expression and check values to ensure there are values for past expiry_dates and no other selections are messing with the result.
Also you can add your expression without label and check how is QV expanding "<=$(=Today())" in the column title.
And check than Expiry_Date is a date, add this field in a list box, if its aligned right, is a date, if it's left-aligned is a text.
=Sum({$<Expiry_Date={"<=$(=Date(Today(),'DD/MM/YYYY'))"}>}SOH)
Replace DD/MM/YYYY by format of Expiry_Date
Hi Ruben
My Expiry_Date is a date on list box it's aligned right and show "MM/DD/YYYY hh:mm:ss" format. I also removed the label to check the syntax seems okay to but still no result for the expression.
hi anbu
I copied your expression and still show 0 as the results
Hi
Assuming you tested the straight table as suggested by Ruben Marin, then test this expression.
Sum({$<Expiry_Date={"<=02/10/2015"}>} SOH)
Another test you can do:
Create a list box containing ExpiryDate, select the box and type "<=02/10/2015" (including the quotes), to see what dates are retuned
HTH
Jonathan
Hi,
the issue is definitely the format of the Expiry_Date column. I would remove the timestamp from this field. Use something like this in your load script.
Example:
LOAD
date(floor(Expiry_Date)) AS Expiry_Date
FROM DataSource;
Also I find that debugging is often easier when using variables. Therefore I would change your code from
Sum({$<Expiry_Date={"<=$(=Today())"}>}SOH)
to something like this: Sum({$<Expiry_Date={"<=$(vToday)"}>}SOH)
where you create a new variable vToday defined as =Today()
=Sum({$<Expiry_Date={"<=$(=Date(Today(),'MM/DD/YYYY hh:mm:ss'))"}>}SOH)