Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
Try using doubles quotes with the nDeliveryDate set modifier.
sum({$<[Stock.]={">0"}, Type = {S}, nDeliveryDate={"<=$(VNumToday)"} >}[Value])
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,
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
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,
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