Set Analysis Expression for Date range - Not working

[#1]

I have created a date filter (2 calendar controls) and I wanted to filter out the data in Pivot based on the date ranges

Value for vStartDate & vEndDate can be obtained from the 2 calendar controls (the values are shown as numbers instead of Date format).

When I issue the following, nothing is being displayed

Sum({$<DateCol ={">=$(=Date(vStartDate)) <=$(=Date(vEndDate))"}>} Sales_Amount)

but if I issue the below code, then the data is populating properly.

Sum(IF(DateCol >= Date(vStartDate) and DateCol <= Date(vEndDate), Sales_Amount)

I am new to QlikView and Set Analysis. I tried putting single quotes and double quotes, but nothing is coming.

Please anyone help. Is expression case sensitive? (Here the case is correct only though)

[#2]

Sum({<DateCol ={">=$(vStartDate)<=$(vEndDate)"}>} Sales_Amount)

Try the above.

Edit: If this does not work, try creating a new field of DateCol in the same formate as the variables.

[#3]

Thanks Marc, its working now.

I tried

=Sum({$<DateCol={">=$(Date(vStartDate))<=$(Date(vEndDate))"}>} Sales_Amount)

instead of

Sum({$<DateCol ={">=$(=Date(vStartDate)) <=$(=Date(vEndDate))"}>} Sales_Amount)

The extra = before Date() made the equation not workable... But the Set Analysis was misleading and so I put that = sign

Thanks to http://community.qlik.com/message/204325#204325

[#4]

Oops.. Its not working

Since I saw some value in the table, I thought it's working.

The Date Filter is not working. But if a date is selected from a List box, the values are changing. Else it is showing the same value whatever be the date changed.

For beautification, I tried putting extra space before <= and it is not returning any value

Any help please? I need it urgently.

One quick question. Is set analysis only for Fact data or even for Dimension data. Can we apply the Set analysis for a dimension, so the all the data pertaining to the dimesion is filtered properly? Is that possible?

[#5]

What formats are your variables recording, vs which format is the field DateCol?

if the variables are

41017

and the date is

4/18/2012

then it might cause issues

[#6]

Yes Marc, it is same as you have specified. I don't know how to convert the vStartDate to Date format.

I have written like this

SQL SELECT

    CONVERT(DateTime,CONVERT(VARCHAR,GETDATE()-5,101)) AS StartDate,

    CONVERT(DateTime,CONVERT(VARCHAR,GETDATE()-1,101)) AS EndDate

FROM "My_DB".dbo."Dim_Date";

LET vStartDate = FieldValue('StartDate', 1);

LET vEndDate = FieldValue('EndDate', 1);

When I Reload, it is populating correctly. But after first time, if I change the value in the calendar control, the date values are changing to integer value automatically.

[#7]

Hi Marc,

Thanks for your suggestion. Atlast it is working. As per your suggestion, I created one more column of the date as integer field and used it.

Viola! its working now

Sum({<DateCol2 ={">=$(vStartDate)<=$(vEndDate)"}>} Sales_Amount)

Here DateCol2 is an integer column

SQL SELECT CONVERT(INT,DateCol) AS DateCol2,*

FROM "BK_DB".dbo."Dim_Date";

But, I really wonder why

Sum({<DateCol ={">=$(Date(vStartDate))<=$(Date(vEndDate))"}>} Sales_Amount)

is not working, though both are in Date format only .

Do I need to specify special formatting like "MM/DD/YYYY" inside date function?

Sigh.... Let me try and post the output

Edit: Oops... Its not working with date format....

[CELAMBARASAN]

Hi,

     Did you checked the loaded DateCol field is in dateformat?if not you need to format on loading using Date#().

Celambarasan

[#9]

My DateCol shows M/D/YYYY hh:mm:ss AM/PM value.

And as you see in the above code, I am converting the StartDate & EndDate to DateTime format only which will also return in the above said format.

Everything said, the filter starts showing different values when I change the date filter, but the value showing is not correct.

Please anyone let me know how to achieve what I am expecting - I need YOY & YTD in 2 grids

[CELAMBARASAN · Accepted solution]

Hi,

     Its in timestamp format.Check with this.

=Sum({<DateCol ={">=$(=TimeStamp(vStartDate))<=$(=TimeStamp(vEndDate))"}>} Sales_Amount)

Celambarasan