topic Re: Still more nightmare with dates in QlikView

Still more nightmare with dates

Anonymous — Tue, 22 Sep 2015 12:07:24 GMT

Can't seem to be able to use a date in a calculated dimension:

1) Load statement

date#(<Input String>,'DDMMYYYY') as ExportDate

2) Variables

SET lastExport = max({1} DateExport )

3) Calculated dimension in my chart

=if(DateExport=$(lastExport), Catégorie)

//This yields a msg "Error in calculated dimension"

=if(DateExport='$(lastExport)', Catégorie) //Notice the single quotes

//This yields no result (condition seems to be always wrong)

Help!

Re: Still more nightmare with dates

tresB — Tue, 22 Sep 2015 12:13:09 GMT

Is this a typo?

SET lastExport = max({1} DateExport )

max({1} ExportDate ) // right one

Re: Still more nightmare with dates

hic — Tue, 22 Sep 2015 12:15:38 GMT

You cannot use set analysis in the script. And you cannot use an aggregation function in a Set or a Let statement.

HIC

Re: Still more nightmare with dates

Anonymous — Tue, 22 Sep 2015 12:20:51 GMT

Yes Typo when I created the case here, but no typo in my code.

Re: Still more nightmare with dates

sasiparupudi1 — Tue, 22 Sep 2015 12:21:55 GMT

load

date#(yourDateString,'DDMMYYYY') as ExportDate,

....

...

from your data source;

in the front end I assume you declared a variable

as lastExport = max({1} ExportDate)

in your calculated dimension

=if(ExportDate='$(lastExport)', Categorie)

=if(ExportDate=$(=lastExport), Categorie)

hth

Sasi

Re: Still more nightmare with dates

tresB — Tue, 22 Sep 2015 12:22:07 GMT

Hi hic‌, he is probably just writing an expression (a mere string to hold in a variable) at the back-end which is to be used at the front-end.

Re: Still more nightmare with dates

tresB — Tue, 22 Sep 2015 12:24:27 GMT

Well then try like:

=if(DateExport=$(=lastExport), Catégorie)

Note, an additional '=' sign before the variable name. This should work if there is SINGLE Catégorie against your max date.

Re: Still more nightmare with dates

Anonymous — Tue, 22 Sep 2015 12:24:33 GMT

Sorry for the confusion, in my code there is no Typo, ExportDate everywhere, and with the results I described in my original post

Re: Still more nightmare with dates

sasiparupudi1 — Tue, 22 Sep 2015 12:31:10 GMT

well then I would say to inspect the following

what is the value in the variable lastExport

and the value in the dimesion ExportDate

are they both in the same format?

Re: Still more nightmare with dates

Anonymous — Tue, 22 Sep 2015 12:31:41 GMT

No doesn't work like this either, "Error in calculated dimension"

Re: Still more nightmare with dates

sasiparupudi1 — Tue, 22 Sep 2015 12:36:24 GMT

May be try like this

=if(DateExport=$(lastExport), [Catégorie])

Re: Still more nightmare with dates

tresB — Tue, 22 Sep 2015 12:37:44 GMT

Sorry, one more correction.

Redefine your variable like:

SET lastExport = '=max({1} DateExport )'; // note equal sign and single quotes

Then expression:

=if(DateExport=lastExport, Catégorie) // '$' not required

Re: Still more nightmare with dates

simenkg — Tue, 22 Sep 2015 12:45:23 GMT

Perhaps you are not flooring your dates.

load

Date(Floor(date#(yourDateString,'DDMMYYYY'))) as ExportDate,