topic Re: Date Problem in QlikView

Date Problem

krishna20 — Thu, 14 May 2015 06:51:36 GMT

Hi Folks,

I'm facing a strange problem not able to troubleshoot it where the mistake is.

In my dashboard i'm showing last three years in list box, under that showing calendar objects from selecting the data range.

My requirement is in on active sheet max year should select in listbox and in the From calendar, Date(Yearstart(Max(Date_Field))) to be selected and in the To Calendar Date(Addmonths(Max(Date_Field),0)) need to select .

If i select 2014 , the calendar object should change from 01/01.2014 to 31/12/2014 dynamically.

The above one i achieved. The problem is , the above task i achieved in my personal desktop. When i'm trying to do the same thing in the client's server it's not working. Each and every variable i did a copy & paste. But, it's not working.

Qlikview i'm using personally is 11.20 SR10

My Clients Qlikview is 11.20 SR5

Is this the problem?or anything.Please suggest me .

Regards

Krishna

Re: Date Problem

morganaaron — Thu, 14 May 2015 07:44:40 GMT

Have you got a trigger that selects the max year? And, although it may work, why are you using AddMonths() to return the last date...couldn't you just use YearEnd like you're using YearStart?

Re: Date Problem

krishna20 — Thu, 14 May 2015 09:26:44 GMT

Hi Aaron,

Thank you for reply

The trigger is working at only list box.The value selected in the list box is not selecting at calender objects.

Is there any mistake using addmonths instead of YearEnd?. The expression is working fine.

Re: Date Problem

morganaaron — Thu, 14 May 2015 09:56:18 GMT

Ah I see, so you select a year from the list box and the calendar should pick up the to and from dates from that selection? I can't see any reason why it shouldn't work - can you post your qvw?

Also no - it's not incorrect, but you're using addmonths with a parameter of 0 - i.e. not adding anything, so why use it anyway? The Max() function should be enough to pick up the last date within the year.

Re: Date Problem

Thu, 14 May 2015 10:38:31 GMT

Please check whether client's system date is in the same format that is there on your system. If no, then please change the format.

Re: Date Problem

krishna20 — Thu, 14 May 2015 11:56:20 GMT

Hi Aaron,

This is working perfectly in my desktop version of qlikview.But not in my client's server.

Please find the below samples developed in different services. Please suggest me where i'm doing mistake.

Creating variables and converting dates in right format suggested by Gysbert.

Re: Date Problem

morganaaron — Thu, 14 May 2015 15:29:15 GMT

Hi Krishna,

From what I can see, the issue is in your sheet triggers.

You've got variables that you're passing the values to (vApprStartDate and vApprEndDate) but the trigger you've selected is "Select in Field" - these are variables not fields, so you need to create two 'Set Variable' triggers from the external action type, and enter the value you have there.

Doing that passes the right values to the calendar, and therefore applying it selects the correct range of data.

Re: Date Problem

krishna20 — Thu, 14 May 2015 15:51:06 GMT

Hi Aaron,

But, In SR11 app it's working correctly right? why not in SR5?Please compare the both apps if not.

Re: Date Problem

morganaaron — Fri, 15 May 2015 07:19:19 GMT

No - there are no triggers for it in the SR11 app at all, at least in the one you sent me. In the SR5 one they're incorrect triggers so they won't work. Neither are altering the calendars. I changed the SR5 one to set variable triggers and it worked. I've tested that app in SR10 and SR7 (the only two versions I have) and both worked fine. I seriously doubt it's a SR issue, I'd check that those triggers are correct and the same in both your apps first.

Re: Date Problem

krishna20 — Sun, 17 May 2015 05:24:26 GMT

Hi Aaron,

Sorry i attached another file.Please find in this attachment which i tried in SR11.

Re: Date Problem

ToniKautto — Sun, 17 May 2015 05:39:34 GMT

I think the main problem is that you have assumed that YearStart() returns a date value. The function actually returns a timestamp, so you need to numerically transform that into a integer to match you date values.

Date(Round(Yearstart(Max(Date_Field))))

EDIT: The timestamp decimal can be forced to integer by using Round().

Re: Date Problem

Peter_Cammaert — Sun, 17 May 2015 08:17:52 GMT

Are you saying that the QlikView documentation/help decided to digress from reality again?

QV Help tells me that YearStart returns the timestamp of the first millisecond of the year, which is 00:00:00.000. IIRC that value translates into a zero fraction.

You know something that we don't?

Peter

Re: Date Problem

krishna20 — Sun, 17 May 2015 08:25:59 GMT

Actually my date is in time stamp format from database. That's the reason choosing Yearstart().

Re: Date Problem

ToniKautto — Sun, 17 May 2015 09:09:58 GMT

Well, it does not matter if your incoming format is timestamp. The output form YearStart() is still a decimal value, and you need to ensure it becomes a properly formatted date.

Actually, considering the floating point value I have edited my suggestion above.

Re: Date Problem

Peter_Cammaert — Sun, 17 May 2015 09:35:03 GMT

Of course you were right about the Date(). I was just talking about the Floor() function which - AFAIK - doesn't return an integer.

Re: Date Problem

Peter_Cammaert — Sun, 17 May 2015 09:46:20 GMT

BTW you're sure you want to state that Date(YearStart()) occasionally returns Dec 31st in QlikView?

Re: Date Problem

ToniKautto — Sun, 17 May 2015 10:08:32 GMT

Floor() and Round() returns a number. This number will be a integer if you use the default base and offset.

num floor(x [ , base [ , offset ]])

num round( x [ , base [ , offset ]])

The 31 Dec is the consequence of when the YearStart() timestamp round downwards, as in my example 40522.999999999999. This s why I changed my previous comment to Round(), as this will correct the rounded integer to 40523.

Re: Date Problem

krishna20 — Sun, 17 May 2015 10:25:21 GMT

Hi Tony,

Can you please apply this logic in my sample app SR5. Because , using yearstart() working correctly in SR11 app. Little confused.

Re: Date Problem

ToniKautto — Sun, 17 May 2015 10:40:03 GMT

Does it make any difference if you add the Round() ?

Re: Date Problem

krishna20 — Sun, 17 May 2015 10:58:23 GMT

No Tony, I need to see correct format date in Calendar object.It's not converting or round the digits.