Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have made a variable called 'Month End Date' and written 01/09/2015 in the box with the 3 dots ...
I assume this is text but I thought QV was clever enough to know it is a date?
And I want to write set to recall all "France" Locations and dates <=Month End Date
But I cant seem to convert Month End Date to a date format DD-MMM-YY to place against completion date
When I write in a expression just [Month End Day] and format to number, it changes to the correct number, so why in the set expression will it not see date?
Also why to I have to place quotes ("") around France and [Completion Date]
Thanks,
=Count(DISTINCT {$<[Location]={"France"},[Completion Date]={"<='$(Month End Date)'"}>}[ID])
if you use a number in set analysis against a date field, it will work just fine.
But if you use text (and yes, it is not that clever to recognise it as date) then you need to point out to QlikView that this is a date. To do so, use the date# function :
=Count(DISTINCT {$<[Location]={"France"},[Completion Date]={"<=$(date#('$(Month End Date)', 'DD-MMM-YYYY'))"}>}[ID])
Just make sure that the date format is what you need.
Even better, just add the date# function in variable $(Month End Date) to avoid the 2 $ signs logic
Hi, if I put the date function in a variable, then wont that be open to input errors for a end user?
This the only code I managed to get working:
Now I want to add a variable called [ADD DAYS
In the inputbox I enter 61.
I have been trying to add to the [Completion Date] or subtracting from [Month End Date] out side the Date#
But no luck, how do I do it?
=
Count(DISTINCT {$<[Location]={"France"},[Completion Date]={'>$(=Date([Month End Date],'DD-MMM-YY'))'}>}[ID])
This is how its done
What took time to understand is that QV converstes 'date' plus a number to a number.
I have to then format as a date to make the set analysis work, as the set is a date DD-MMM-YY
[Completion Date]={'>$(=Date(Date([Month End Date],'DD-MMM-YY')-[Days],'DD-MMM-YY'))'}