Calculate Qty

[#1]

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

[rubenmarin]

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.

[anbu1984]

=Sum({$<Expiry_Date={"<=$(=Date(Today(),'DD/MM/YYYY'))"}>}SOH)

Replace DD/MM/YYYY by format of Expiry_Date

[#4]

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.

[#5]

hi anbu

I  copied your expression and still show 0 as the results

[jonathandienst]

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

[Anonymous]

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

[anbu1984]

=Sum({$<Expiry_Date={"<=$(=Date(Today(),'MM/DD/YYYY hh:mm:ss'))"}>}SOH)