Variable for date range selection

[#1]

Dear Community,

I am using variable for date selection, but facing a little bit problem in the variable date format.

There are 2 date ranges will be selected for comparison in bar chart - Analysis period and comparison period.

Initially i am not sure why when trying to filter analysis date, the bar chart will become empty.

Finally I found that my variable - vStartDate become wrong format:

I really cannot understand why only vStartDate got problem as I set all 4 variables at the same time (vStartDate, vEndDate, vComparisonstartDate, vComparisonEndDate)

Anyone can help?

Thanks and best regards,

Chanel

[Gysbert_Wassenaar]

Probably because your CreatedDate and ShortCreatedDate values are not just dates, but timestamps. You should probably cut of the time part using the floor function first: floor(CreatedDate) as CreatedDate

Also it's recommended to create the dates as numbers and only add the formatting in the calendar, table and chart objects. You'll have less complications in expressions because you don't need to keep date formats in mind there then.

[fheidenstecker]

Hi,

I think because the fields that hold your dates are Date AND time fields, you should try using the floor function on your expression. In the example you attached i can't find where the variables are set, so maybe this example helps

instead of num(now())

use

num(floor(now()))

Cheers,

Fabian

[#4]

Thanks for prompt response!

I already set the ShortCreateDate to YYYY-MM-DD

Anything goes wrong in the script?

Thanks and best regards,

Chanel

[#5]

Hi Fabian,

Thanks very much for your idea!

Variable is set at Document Properties - Trigger - Field and variable event trigger.

I am not sure where to add on num(floor(now()))... in script? document properties' trigger? or expression?

Thanks and best regards,

Chanel

[Gysbert_Wassenaar]

The date function only sets the format. It does not cut of the time part. So you're giving a timestamp the YYYY-MM-DD format. Try creating only numeric dates in the script and cut of the time fractions using the floor function. See this blog post: Get the Dates Right