Set analysis with DD/MM date format

[sergio0592]

Hi all,

I have to date field in a .qvw. Thes result of following SQL statements :

to_char(S.DATE_START,'DD/MM/YYYY') as DATE_START,
to_char(S.DATE_START,'DD/MM') as DATE_START_DD_MM

When i use DATE_START is Set analysis, no problem it works and i can make a selection on this field in a list box:

But when i try to use DATE_START_DD_MM, it don't works.

So, it considered as text (see the snapshot below with data on left)

And when i try to perform a string to number conversion with Date# (=Date#(DATE_START_DD,'DD/MM') ),

i can't make select too

Thanks for your help.

[kaushiknsolanki]

Change below statement

to_char(S.DATE_START,'DD/MM') as DATE_START_DD_MM

To

Date(S.DATE_START,'DD/MM') as DATE_START_DD_MM

Regards,

Kaushik Solanki

[sergio0592]

Thanks for your answer. But i'am on Oracle and Date function doesn't exists. Maybe you mean to_date, but i tried and give SQL error 01830. 00000 -  "date format picture ends before converting entire input string"

[kaushiknsolanki]

Hi,

Instead of changing it at oracle side change in Qlik. Like shown below.

Load Field1,Field2,Date(S.DATE_START,'DD/MM') as DATE_START_DD_MM;

Sql Select Field1,Field2,S.DATE_START from xyz;

Regards,

Kaushik Solanki

[sergio0592]

Ok, i have tried in load statement and i am able to make a research in the list box. But, it returns not only date>25/05 because i have 18/05,01/01, 02/01,...

[Anonymous]

Hi Jean,

  Did you tried by changing default DD/MM to MM/DD or vice versa in your "Main" tab?

-Jai

[Peter_Cammaert]

You're initial technique doesn't work because the DATE_START_DD_MM values are passed along from Oracle to QlikView as strings. QlikView doesn't recognise 02/11 as a date value (something is missing for that) and there is no underlying numerical value that says that Year=2017 (as happens with QlikView dual values).

So when you convert 02/11 using Date#(DATE_START_DD_MM, 'DD/MM') and a missing year, then QlikView will assume the year of the lowest numerical date value (0 being equal to 30/12/1899 and 1 being equal to 31/12/1899).

If you use the search box to look for dates after or before a specific date, you must supply a full date value and not what you show in your listbox. In your formatted listbox (last screenshot), you can do a search for '>01/11/1899'. See what you get...

Note that 01/11 = 01/11/1899 corresponds to numerical value -59.

[sergio0592]

Thanks Peter for your answer. Indeed, i understand why i have negative values. So, what i'am trying to achieve is to get e;g values between 01/01 and 27/11 for Year N, N-1, N-2, etc.

And in order to avoid to have several expressions N, N-1, N-2, etc i tried with Year as dimension and only one expression where i use DATE_START_DD_MM in my SA.

But if Qlikview requires a year i will use as many expression as year.

Regards

[Peter_Cammaert · Accepted solution]

I think that is the best thing you can do, just adding expressions for YTOD for current, last and next to last year.

You could make your first technique work by adding a Year as dimension and doing a set analysis based  on daynumber of the year. But then you would have to take into account that every leap year has one day more after Feb. 28th. So that wouldn't work in all cases unless you create a really complex construct.

Alternatively, you could consider using the YearToDate() function in your aggregation (and without set analysis). It has a parameter to shift the year at will. But if you have a large data set, you should avoid the use of IF() calls in aggregation functions...

Three expression with Set analysis based on Today()'s date and AddMonths()/AddYears() to shift a year is easier I think.

Best,

Peter