topic Re: Calculate Qty in QlikView

Calculate Qty

Tue, 10 Feb 2015 08:20:30 GMT

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

Re: Calculate Qty

rubenmarin — Tue, 10 Feb 2015 08:26:13 GMT

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.

Re: Calculate Qty

anbu1984 — Tue, 10 Feb 2015 08:27:21 GMT

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

Replace DD/MM/YYYY by format of Expiry_Date

Re: Calculate Qty

Tue, 10 Feb 2015 09:09:51 GMT

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.

Re: Calculate Qty

Tue, 10 Feb 2015 09:12:28 GMT

hi anbu

I copied your expression and still show 0 as the results

Re: Calculate Qty

jonathandienst — Tue, 10 Feb 2015 09:18:53 GMT

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

Re: Calculate Qty

Anonymous — Tue, 10 Feb 2015 09:29:38 GMT

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

Re: Calculate Qty

anbu1984 — Tue, 10 Feb 2015 10:54:08 GMT

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