topic Re: Set analysis for date field in QlikView

Set analysis for date field

andyrebs — Tue, 27 Nov 2012 22:31:02 GMT

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

Re: Set analysis for date field

swuehl — Tue, 27 Nov 2012 22:35:41 GMT

Try using doubles quotes with the nDeliveryDate set modifier.

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

Re: Set analysis for date field

stevedark — Tue, 27 Nov 2012 23:52:35 GMT

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

Re: Set analysis for date field

andyrebs — Wed, 28 Nov 2012 14:40:13 GMT

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

Re: Set analysis for date field

stevedark — Wed, 28 Nov 2012 21:46:54 GMT

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

Re: Set analysis for date field

andyrebs — Thu, 29 Nov 2012 10:45:01 GMT

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