topic Re: Date Comparison in Set Analysis in QlikView

Date Comparison in Set Analysis

Tue, 09 Apr 2013 01:58:55 GMT

Kindly would you suggest what's wrong with this expression?

[Program Date] = {"<=$(date#(vSelectedDate, 'dd/MM/yyyy'))"}

Where vSelectedDate holds value as : 31 Jan 2013

Re: Date Comparison in Set Analysis

Anonymous — Tue, 09 Apr 2013 03:14:50 GMT

Well, there can be other things as well (since we can't see the format of [Program Date]) but one thing is that you are trying to interpret the vSelectedDate as 'dd/MM/yyyy' when you say that the format is actually '31 Jan 2013'.

So, I assume that the dd/MM/yyyy format is actually the format you have in [Program Date] and you're trying to convert 31 Jan 2013 to match this?

in that case try:

date(date#(vSelectedDate, 'DD MMM YYYY'), 'DD/MM/YYYY')

Re: Date Comparison in Set Analysis

Tue, 09 Apr 2013 04:06:41 GMT

HI ,

Please try the below code

[Program Date] = {'$(=date#(vSelectedDate, 'DD MMM YYYY'))'}

Please let me know if there is anything .

Re: Date Comparison in Set Analysis

sushil353 — Tue, 09 Apr 2013 04:37:21 GMT

hi,

make sure that the data type and format of [Program Date] and vSelectedDate should be same..

try to see the format using test box : =vSelectedDate..

and then use in setanalysis as

[Program Date] = {"<=$(vSelectedDate)"}

also try this:

[Program Date] = {"<=date#($(vSelectedDate), 'dd/MM/yyyy'))"}

HTH

Sushil

Re: Date Comparison in Set Analysis

kaushiknsolanki — Tue, 09 Apr 2013 05:50:48 GMT

Hi,

This is the issue with date formating.

Both the dates (Fields and variable) should be in same format.

So if your variable holds the date make sure that its into same format as the date format of your field.

Example Variable is 31/01/2013 then expression is

[Program Date] = {"<=$(vSelectedDate)"}

Date#() is needed only when your variable holds date in text format.

Date#() is used to convert the text into date.

The example of this is below.

Assume [Program Date] has date in DD/MM/YYYY format. and variable has text as 31-Mar-2013 then

[Program Date] = {"<=$(Date(date#(vSelectedDate, 'DD-MMM-YYYY'),'DD/MM/YYYY'))"}

Hope this is clear.

Regards,

Kaushik Solanki

Re: Date Comparison in Set Analysis

preminqlik — Tue, 09 Apr 2013 08:00:00 GMT

tell me what exactly you are comparing

i think you no need to do set analysis for this
just do like this :

if([program Date]=date(vSelectedDate, 'dd/MM/yyyy'),TRUEEXPRESSION,FALSEEXPRESSION)

i think "program date" field conains numeric values of date

tel me exactly what you are doing and give the example values of "Program Date" field

--prem

Re: Date Comparison in Set Analysis

Tue, 09 Apr 2013 09:20:47 GMT

To convert vSelectedDate into QV date format you should use: date#(vSelectedDate, 'DD MMM YYYY') due to your format like '31 Jan 2013'.

And if [Program Date] has default document format, then full expression shoud be:

[Program Date]={"<=$(=date(date#(vSelectedDate, 'DD MMM YYYY')))"}

Re: Date Comparison in Set Analysis

CELAMBARASAN — Tue, 09 Apr 2013 10:27:13 GMT

try

[Program Date] = {"<=$(=Date(date#($(vSelectedDate), 'DD MMM YYYY'))"}

Re: Date Comparison in Set Analysis

Tue, 09 Apr 2013 10:48:05 GMT

try a single cote instead of "

chris

Re: Date Comparison in Set Analysis

Tue, 09 Apr 2013 11:38:09 GMT

for any functions in set analysis u can use = sign

[Program Date] = {"<=$(=date#(vSelectedDate, 'dd/MM/yyyy'))"}

Re: Date Comparison in Set Analysis

jagan — Tue, 09 Apr 2013 11:49:15 GMT

Hi,

Try like this

Sum({<[Program Date] = {"<=$(=Date(date#(vSelectedDate, 'dd MMM yyyy')))"}>} Measure)

Please check whether the formats of date in [Program Date] and Date(date#(vSelectedDate, 'dd MMM yyyy')) are same. If both are same then the above expression works.

Hope this helps you.

Regards,

Jagan.