Input date entered as text and try to convert in set analysis

[tinkerz1]

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

[giakoum]

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

[tinkerz1]

Hi, if I put the date function in a variable, then wont that be open to input errors for a end user?

[tinkerz1]

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

[tinkerz1]

  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'))'}