Filtering 2 dates with calendars and showing last year

[#1]

Hi,

I am trying to use this solution as calendar filter Date Selections - By Period & Range and it works well, but at the same time I am displaying a table with year volumes and last year volumes and it does not appear anything for last year, while if I don't use the calendars for filtering and I just use the listboxes instead for year, month and day then it works.

My expression for last year is: sum( {$< Year={$(=Only(Year)-1)} >}  Sales )

Calendars are filtering like: >=2014-01-01<=2014-02-28

Am I missing something or it's just that calendar filters do in a way that it prevents this?

Thanks in advance

[stigchel]

Best is always to load your dates in the same format as your system settings at the beginning of your load script

SET DateFormat='DD/MM/YYYY';

Then you will have less trouble later.

For your expression, first the quotes. Set expression using a search should be in double quotation marks (not yet required, that's a bug), for literals single quotes. This is a search. Which is good, because you want the date format in single quotes:

=">=$(=date(vDateStart,'YYYY-MM-DD'))"

If that does not help, you may need the Date# function (interpreting strings), so

Date(Date#(

Another tip is to leave the expression label blank, like in my example, it will then show you how your variables are interpreted in the heading.

Also See

Get the Dates Right

Why don’t my dates work?

[#3]

Hi,

First thanks for the quick answer.

In my case I am not using a set analysis, that expression is fired when clicking the button Apply filter.

I've reloaded the document with the date formating 'DD-MM-YYYY'

The field is recognized well by QV, I displayed it as timestamp and date and it's ok (following the links you provided me), as well as if I do Num(date), it looks like it's more like when filtering it's treated as text so I can't

If I directly filter in the listbox I can do >=2014-01-01 and it works.

I tried these ones :

='>=2014-01-01' -> Works

='>=$(vDateStart)' -> Doesn't work. In a label it displays as 41640

='>="$(vDateStart)"'-> Doesn't work

='>=''$(vDateStart)''' -> Doesn't work

=date($(vDateStart)) -> works

='=date($(vDateStart))' -> Doesn't work

='$(date($(vDateStart)))' -> Doesn't work.

So seems I need to format it to YYYY-MM-DD for filtering.

What am I missing?

[#4]

I answer myself:

Now with the changed date format in the script and the following expression the apply button works:

='>=$(=date($(vDatedStart))) <=$(=date($(vDatedEnd)))'