Set analysis for date field

[andyrebs]

Hi,

I've check in the community, but, even if I found many discussions about this iusse, I can't figure it out.

I have this expression:

sum({$<[Stock.]={">0"}, Type = {S},    nDeliveryDate={<=$(VNumToday)}       >}[Value])

But It returns a value as 0.

What is wrong in  the part of  date set analysis ?

Many thanks

Andrea

[swuehl]

Try using doubles quotes with the nDeliveryDate set modifier.

sum({$<[Stock.]={">0"}, Type = {S},    nDeliveryDate={"<=$(VNumToday)"}       >}[Value])

[stevedark · Accepted solution]

Hi there,

You need to be very careful with the format of your date field and the format of the date variable that you are comparing it to.  In Set Analysis 27/11/2012 does not equal 27 Nov 2012.

Suggest forcing a format for the date field in your load script, eg:

Date(nDeliveryDate, 'DD-MMM-YYYY') as nDeliveryDate,

And then the same in your variable definition:

=Date(today(), 'DD-MMM-YYYY');

It can also be useful to use an Input Box to keep a close eye on what you actually have in your variables.

Hope that helps,

Steve

[andyrebs]

Many thanks both Steve and sweuhl.

I tried your suggestions, and now I have the value, unfortunately the set analysis does not work properly.

In fact the value that results from the expression brings me out not only the value of the orders with the delivery date <= of today but even the value of the orders after today.

See the attached file inside my question post

[stevedark]

If you reference a fieldname in Set Analysis that doesn't exist in your data model this is simply ignored.  Could it be that nDeliveryDate appears slightly differently in the data model (note case sensitivity)?  Looking at the spreadsheet you uploaded, should the field read simply nDelivery?

If you can upload a sample QlikView app it should be relatively quick to get to a solution.

Regards,

Steve

[andyrebs]

Hi Steve,

You're right, I wrote the variable
VNumToday

instead

vNumToday, that I laod on the script.

Regarding nDelivery or nDeliveryData, there were only my traslations from my Language.

Many Thanks

Andrea