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]

Fantabulous.

Thanks a lot Celambu / Adhi, it works like a charm.

Is there a separate documentation for various conversions & formatting. I am very new to QlikView, working for past few days only. Please guide me to learn QlikView quickly.

Thanks once again.

A quick question: Can we include another year in the same expression for YOY comparison. I am achieving this now through 2 IFs as mentioned in my initial post (and this is working correctly).

IF((DateCol >= Date(vStartDate) and DateCol <= Date(vEndDate)) or (DateCol >= AddYears(Date(vStartDate),-1) and DateCol <= AddYears(Date(vEndDate),-1))

I need to achieve YOY & YTD in the grid. Can you please guide me. Thanks in advance

[CELAMBARASAN]

Hi,

     Simply use this

     =Sum({<DateCol ={">=$(=TimeStamp(AddYears(vStartDate,-1)))<=$(=TimeStamp(vEndDate))"}>} Sales_Amount)

Qlikview help file and this community is enough to learn Qlikview.

Celambarasan

[#4]

Thanks. Is this a separate column or in the same column.

The main reason I am asking this is, in my previous query I wrote a single expression and just dragged the year dimension and it populates Sales for both the years.

Like that is there any way where I can specify AND / OR conditions inside an expression.

Edit: I have created another Measure for YOY - previous year and removed the year dimension and its working fine.

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

Sales (PY): =Sum({<DateCol ={">=$(=TimeStamp(AddYears(vStartDate,-1)))<=$(=TimeStamp(AddYears(vEndDate,-1)))"}>} Sales_Amount)

[CELAMBARASAN]

Hi,

=Sum({<DateCol ={">=$(=TimeStamp(AddYears(vStartDate,-1)))<=$(=TimeStamp(vEndDate))"}>} Sales_Amount)

Above expression itself work like your expression

IF((DateCol >= Date(vStartDate) and DateCol <= Date(vEndDate)) or (DateCol >= AddYears(Date(vStartDate),-1) and DateCol <= AddYears(Date(vEndDate),-1))

for both the years.

Celambarasan

[#6]

The query is partially right. The thing is if we give a date range like Apr 1st to Apr 5th, then it is taking the date range as

Apr 1st 2011 till Apr 5th 2012.

But I need Apr 1st 2011 to Apr 5th 2011 and Apr 1st 2012 to Apr 5th 2012. I dont want the data between 5th Apr 2011 and Mar 31st 2012

Hence I achieved as I mentioned in my above post. Thanks, you have given an insight of how to arrive, which is very helpful

Any specific doc or link for formatting and conversions?

[#7]

Hi,

I want to achieve this. Can you please help me? I don't want the greyed out portion as it displays only zeros. Can we achieve through Set Analysis?