topic Re: Using dates as variables in a straight table - Calculation timeout in QlikView

Using dates as variables in a straight table - Calculation timeout

Anonymous — Mon, 26 Jun 2017 10:34:38 GMT

I need to display a straight table to display data between 2 dates. These dates are set via variables and a calendar object.

I have added a calculated a dimension that sets the dimension to null when the date is not between the variables:

=if(PK_CALENDAR>=vStart and PK_CALENDAR <=vEnd,1,null())

with PK_CALENDAR being my date field in my model, and vStart and vEnd being the calendar variables.

This is then supressed when values are null, so that only rows between those dates are displayed.

My issue is that this is proving very slow, and some users are having calculation timeouts based on this dimension and other similar ones. Is there an alterative method?

(ideally without changing my entire data model!)

Re: Using dates as variables in a straight table - Calculation timeout

migueldelval — Mon, 26 Jun 2017 10:38:59 GMT

Hi Nick,

Try to use yin your script:

if(PK_CALENDAR>=vStart and PK_CALENDAR <=vEnd,1) as FLAG_DATE;

If you want attach a data sample, I will try to help you.

Regards

Miguel del Valle

Re: Using dates as variables in a straight table - Calculation timeout

Anonymous — Mon, 26 Jun 2017 10:54:49 GMT

Do you mean as part of the load script?

How would we get it to interact with variables?

Re: Using dates as variables in a straight table - Calculation timeout

migueldelval — Mon, 26 Jun 2017 11:36:50 GMT

Hi Nick,

Check please,this example and ask me If you have some trouble,

Regards

Miguel del Valle

Re: Using dates as variables in a straight table - Calculation timeout

Kushal_Chawda — Mon, 26 Jun 2017 12:14:04 GMT

You can use script method like below

From Date & To Date Selection Script Solution

Re: Using dates as variables in a straight table - Calculation timeout

Anonymous — Mon, 26 Jun 2017 12:29:23 GMT

But this does not change dynamically when the variables are changed?

Re: Using dates as variables in a straight table - Calculation timeout

Anonymous — Mon, 26 Jun 2017 13:10:55 GMT

I have incorporated this method in to my data structure, however the data does not change on these selections.

Can I have an example of a table/expression that will work with this method

Re: Using dates as variables in a straight table - Calculation timeout

Kushal_Chawda — Mon, 26 Jun 2017 13:12:42 GMT

You just need to replicate the script, and just need to select from date & to date. No variable required.

Re: Using dates as variables in a straight table - Calculation timeout

Anonymous — Mon, 26 Jun 2017 13:20:56 GMT

I believe there is something wrong with the way I have incorporated it to my current data model.

The Key list box is changing dates correctly when selecting from the calendar, but the Date list box does not.

I have had to adapt it as I have a working existing calendar script that I do now want to change, as different features are used throughout the project

Re: Using dates as variables in a straight table - Calculation timeout

Anonymous — Mon, 26 Jun 2017 15:19:54 GMT

I have attached my data model script (minus the fact tables and dashboard objects)