Finding specific date values

[Anonymous]

Hi all,

I'm trying to work out consistent logic in order to write code that will allow me to enter data values into set analysis expressions.

The example below is a very simplified one, I've tried to break the problem down to it's basic component so that hopefully someone can help me and I can work out what is going on.

I think my problem really comes down to how QV 11 stores and processes dates and then how you use that knowledge in your application.

I have some very simple data as shown below:

DateFld	ProductID	BG
2014-10-28	1	A
2014-10-29	2	A
2014-10-30	3	B
2014-10-31	4	B
2014-11-01	5	C
2014-11-02	6	D

In my qvw file (attached) I've got three text box objects displaying the result of a set analysis expressions as shown below.

='SUM of BG=A: ' & Sum( {1<BG = {'A','B'}>}  ProductID)

This correctly displays a value of 10

='Using ISO date format: ' & Sum( {1<DateFld = {'2014-11-01'}>}  ProductID)

This correctly displays a value of 5

In my second expression above, I'm comparing the value of DateFld to a simple literal and all works ok - PROVIDING that I use the date format shown (i.e. YYYY-MM-DD).

='Using system date format: ' & Sum( {1<DateFld = {'01/11/2014'}>}  ProductID)

This displays a value of 0.

-- I'm in the UK, my Windows system is set to use a 'short date' format of 'dd/MM/yyyy' and the DateFormat command in my load script uses DD/MM/YYYY.

I think I'm correct in saying that QV11 stores dates using an MS serialdate value, but as the comparison above is to a text string something needs to be converted in order to a value to be returned.

I can 'sort of' understand why  the ISO date format would work, it is after al a fairly 'standard' date format.

But why doesn't it work when I'm using a value in my system date format?

As I said above, this is a very simplified version of what I'm ultimately trying to do which will be to generate the SUM of a particular field for all rows in the last 'n' days.

ALl pointers and help greatly appreciated.

Cheers,

Dave

[sunny_talwar · Accepted solution]

I think all you need is to do this:

TestDates:

LOAD Date(DateFld) as DateFld,

    ProductID,

    BG

FROM

[test_dates.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

I believe once you do this transformation, the date field will pick the date format from the environmental variable:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

But unless you do that, QlikView will save the date format (if it has read it as date) in its original format. Just because you change the format in one particular object, it doesn't mean that QlikView will change its format when using it in Set Analysis.

One alternative to not using Date() is to change the date format using Document properties. This changes the format for the complete document.

Once you do this, now it will show correct output for this -> ='Using system date format: ' & Sum( {1<DateFld = {'01/11/2014'}>}  ProductID)

and incorrect for this -> ='Using ISO date format: ' & Sum( {1<DateFld = {'2014-11-01'}>}  ProductID)

[Anonymous]

Hi Sunny,

Many thanks for that, your change in the script works fine.

So it would appear that with my original LOAD script the DateFld values were being loaded as text strings and not dates - which is why the only comparison that worked was when I was using a string in the same format as per the input data file.

On to the next bit...

Cheers,

Dave