topic Re: Variable based on date in QlikView

Variable based on date

edemerdjieva — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a chart and KPI is filtered based on dates selection (as in the screenshot).

I use two variables for beginning and end date selection. The problem is that the date selected is not well interpreted and thus no results in the chart. I checked and data is being present : I made a simple chart and used a date selection from a calendar object and it's working.

The question is what's wrong with the variables ?

Please find attached the example as a screenshot.

Re: Variable based on date

Miguel_Angel_Baeyens — Wed, 24 Jul 2019 11:34:22 GMT

Are the values for the dates exactly in the date format expected? "24/07/2019" is a valid format, but so are "07/24/2019" or "43670".

According to the definition of the variable v_PC_DF the date will have the format "43670", so does the field "Date" have the same values stored?

Re: Variable based on date

sunny_talwar — Wed, 24 Jul 2019 11:36:04 GMT

May be change your variable to this

TimeStamp(Today(), 'DD/MM/YYYY hh:mm:ss')

I can't see the whole format for Date field, but make sure to use TT if you have AM, PM at the end.

Re: Variable based on date

Elina — Thu, 25 Jul 2019 07:55:42 GMT

@Miguel_Angel_Baeyens

Hello,

Yes, the field Date has the same format 43670 in the qvd file. But when loaded in the application it changes as you can see in the simple chart above. QV must be doing some formatting which I want to avoid.

Re: Variable based on date

Elina — Thu, 25 Jul 2019 09:31:20 GMT

@sunny_talwar

Actually, I don't want the timestamp. I would like to stick to simple number. And as I was explaining above, QV automatically considers the Date as Timestamp although in the qvd it's a number.

Re: Variable based on date

Miguel_Angel_Baeyens — Thu, 25 Jul 2019 10:56:18 GMT

Check how it looks like when you use the field value or the variable in a text object, not in a chart. I'd expect it to show "43670", but might not be the case.

Re: Variable based on date

sunny_talwar — Thu, 25 Jul 2019 11:37:15 GMT

How about if you load it as Num(Date) as Date so that it is stored as a number...

Re: Variable based on date

Elina — Fri, 26 Jul 2019 08:09:18 GMT

@sunny_talwar

Num(Date) worked but I had to find all Date fields loaded and modify each one of them. Otherwise it doesn't work.

Thanks for your help !

Re: Variable based on date

sunny_talwar — Fri, 26 Jul 2019 11:11:02 GMT

I guess I am confused as to what exactly is the issue? I mean you need to have a single format for your date field to make it work in your set analysis... and you don't want to use Num()?

Re: Variable based on date

Elina — Wed, 31 Jul 2019 13:01:13 GMT

@sunny_talwar

I used the Num() function and it worked. But since I have some other dates loaded with another format they all needed to be formatted with Num() :-).

Re: Variable based on date

sunny_talwar — Wed, 31 Jul 2019 13:12:45 GMT

Yup, I agree that they all need to be changed using Num()