Dashboard Prompt(In Between valus) not filtering report values

Question

Hi All,

I have a 'In Between' date prompt which has default date of today’s date(current date)  and today’s date (current date)– 30 days.

On the date column 'is prompted' filter has been applied

When I checked the query it was " Datecolumn  between DATE '1974-10-20' and DATE '1950-09-20' " 
 But query is giving no results even i have data between dates.

To create in between prompt below method has been used:

Used a column prompt, select the "is between" operator,

And select "choice list" for user input.

Select "SQL Results" for Choice List Values and insert this SQL

SELECT "Table."Date" FROM "Subject Area" UNION SELECT TIMESTAMPADD (SQL_TSI_DAY,-30,CURRENT_DATE) FROM "Subject Area" UNION SELECT CURRENT_DATE FROM "Subject Area”

Now to set the defaults! Select "SQL Results" for Default Selection as well. Insert in the first text box to current  date using this SQL:

SELECT CURRENT_DATE FROM "Subject Area”

Insert 30 days before date using this SQL into the next text box:

SELECT TIMESTAMPADD (SQL_TSI_DAY,-30,CURRENT_DATE) FROM "Subject Area"

Then I created an analysis and filtered for the desired date column as "is prompted"

But I am getting No Result please help me here

Saurav Singh chandel · Answer

Thanks , It worked.

Gianni Ceresa · Answer

Saurav Singh chandel wrote:When I checked the query it was " Datecolumn between DATE '1974-10-20' and DATE '1950-09-20' " 
 But query is giving no results even i have data between dates.

Well, you maybe have data between September 1950 and October 1974, but from a logical point of view no results is right !

BETWEEN 1974-10-20 AND 1950-09-20 : no result because the first value is greater than the second, so your between is like a 1 <> 1 condition

BETWEEN 1950-09-20 AND 1974-10-20 : as long as you really have data you get your rows back

It's really a simple logic on how the BETWEEN operator work.