topic Re: Today as default when page loads in QlikView

Today as default when page loads

josephinetedesc — Thu, 27 Aug 2015 04:59:00 GMT

I am trying to set the default for a page - I want the default date to appear in the Calendar:

I have tried setting a variable called vMyDate = Today()

I have then gone to the page and tried to get the Calendar object to display today's date and therefore the chart would update to show today's values.

I have also tried making a button and using the trigger to set the date - (I think i remember something about dates being treated as strings???)

Complication: The calendar object reflects the field Appt_DtTm (this is decimal and shows date and time). I need the date and time reflected in the chart - see graphic

=time(App_DtTm,'HH:MM')

anyhelp gratefully appreciated.

Re: Today as default when page loads

jonathandienst — Thu, 27 Aug 2015 05:15:44 GMT

Its not a good idea to store date and time in the same field - this is due to the high cardinality of timestamps. You will get better performance by splitting it into date and time and rounding the time to the smallest duration of intersest.

To get separate fields

LOAD ...

Date(Floor(Appt_DtTm)) As Appt_Dt,

Time(Round(Frac(Appt_DtTm), 1/24) As Appt_Time // to the nearest hour

This assumes that the Appt_DtTm is recognised as a time stamp.

Re: Today as default when page loads

josephinetedesc — Thu, 27 Aug 2015 05:19:11 GMT

But I need the chart respond to the Appt_Time and the Appt_Dt ...

Re: Today as default when page loads

jonathandienst — Thu, 27 Aug 2015 05:22:14 GMT

Date/time values are stored in units of day, so the date is the integer part of the date (Floor()) and the time is the fractional part (Frac()). 24 hours is 1 day, so one hour is the value 1/24, one minute is 1/24/60 (=1/1440) and one second is 1/24/60/60 (=1/86400). So to round time to the nearest hour we say Round(Frac(Appt_DtTm), 1/24) and to display that as a time value, we format it with Time - Time(Round(Frac(Appt_DtTm), 1/24)).

Re: Today as default when page loads

jonathandienst — Thu, 27 Aug 2015 05:26:09 GMT

Josephine Tedesco wrote:

But I need the chart respond to the Appt_Time and the Appt_Dt ...

Is the Appt_DtTm currently linked to the fact values in the chart? The code fragment I gave earlier should be part of the LOAD where you are currently loading Appt_DtTm - but instead of loading Appt_DtTm, you are loading Appt_Dt and Appt_Tm as two fields instead of one.

Then you can make selections on either field or use them as dimensions...

Re: Today as default when page loads

josephinetedesc — Thu, 27 Aug 2015 05:27:04 GMT

yes I understand that ... but I am trying to get the chart to show what occurred hourly on a particular day - so my chart dimension is

=time(App_DtTm,'HH:MM')

so the calendar treate the App_DtTm as a day but the chart actually shows what occurred on that day but by the hour/time

Re: Today as default when page loads

jonathandienst — Thu, 27 Aug 2015 05:34:17 GMT

The problem is Time() does not change the underlying value, so you will be getting different values that look the same, but are not. That's why you need to use the Floor() and Round(Frac()) calculations.

For example, a timestamp of 2015/08/27 07:33:15.01563 will display as 7:33, but so will 2013/12/11 07:33:22.565 and 2015/08/27 07:06:01.0001 -- despite the fact the Time() makes them look the same, they will all have different values.

Re: Today as default when page loads

jonathandienst — Thu, 27 Aug 2015 05:37:49 GMT

Josephine Tedesco wrote:

yes I understand that ... but I am trying to get the chart to show what occurred hourly on a particular day -

And if you use the code i gave you, the time field will be in hours, so all the transactions that happen in that hour will get the same time value and you can use that as a chart dimension in hours.

Re: Today as default when page loads

josephinetedesc — Thu, 27 Aug 2015 05:39:48 GMT

but that is why i want to ensure that the date is Today(2) - therefore the fraction part will always be different.

Re: Today as default when page loads

josephinetedesc — Thu, 27 Aug 2015 05:42:45 GMT

ok - I see that - but that is a different question (probably for another day when I start checking what it is exactly that is wanted - probably better than what I am doing actually) For now though I just want to be able to set the date to Today(2) when I open up that page

Re: Today as default when page loads

jonathandienst — Thu, 27 Aug 2015 05:53:50 GMT

Coming back to the first part of your question:

Josephine Tedesco wrote:

I am trying to set the default for a page - I want the default date to appear in the Calendar:

I have tried setting a variable called vMyDate = Today()

I have then gone to the page and tried to get the Calendar object to display today's date and therefore the chart would update to show today's values.

I have also tried making a button and using the trigger to set the date - (I think i remember something about dates being treated as strings???)

Rather than using a trigger to set the date, use a smart expression for for the variable defining the report date. Let's cal it vMaxDate for this example:

If you want one which will display the most recent date in the model, then define vMaxDate like:

Set vMaxDate = =Date(Max(Appt_Dt )); // the two = signs are not a typo

If your dates may extend to the future, and you want to default today unless something is selected:

Set vMaxDate = =Date(If(GetSelectedCount(Appt_Dt) = 0, Today(), Max(Appt_Dt));

These two expressions will return the selected date if one is selected, but will return something useful if nothing is selected.

The extra sign causes the variable to carry the evaluated value of the expression, so you can use it simply like:

Sum({<Appt_Dt = {'$(vMaxDate )'}>} Amount)

rather than

Sum({<Appt_Dt = {'$(=Date(If(GetSelectedCount(Appt_Dt) = 0, Today(), Max(Appt_Dt)))'}>} Amount)

Re: Today as default when page loads

josephinetedesc — Thu, 27 Aug 2015 06:07:07 GMT

I can see what you are doing - you are getting to the chart expression and eventually I will probably need to do this ... but I only want the date to be the dimension that causes the chart to change - and for this I want the default to be today's date.

At the moment when I choose a date from the Calendar - The chart responds how I want it to. I was hoping that there was a way of just getting the default for the sheet to be today's date. So the chart would open to today's values.

Re: Today as default when page loads

jonathandienst — Thu, 27 Aug 2015 06:12:24 GMT

The second option in my post does just that - it defaults to Today() if nothing is selected....

Re: Today as default when page loads

josephinetedesc — Thu, 27 Aug 2015 06:45:48 GMT

Set vMaxDate = =Date(If(GetSelectedCount(Appt_Dt) = 0, Today(), Max(Appt_Dt));

so I go to Variables Overview

and add VMaxDate as a Variable

and in the Definition add

= =Date(If(GetSelectedCount(Appt_Dt) = 0, Today(), Max(Appt_Dt)))

and in the sheet definition I go to Trigger and then??