Using dates as variables in a straight table - Calculation timeout

[Anonymous]

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!)

[migueldelval]

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

[Anonymous]

Do you mean as part of the load script?

How would we get it to interact with variables?

[migueldelval]

Hi Nick,

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

Regards

Miguel del Valle

[Kushal_Chawda]

You can use script method like below

From Date &amp; To Date Selection Script Solution

[Anonymous]

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

[Anonymous]

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

[Kushal_Chawda]

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

[Anonymous]

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

[Anonymous]

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