topic Re: Set Analysis Expression for Date range - Not working in QlikView

Set Analysis Expression for Date range - Not working

Thu, 19 Apr 2012 14:00:42 GMT

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)

Re: Set Analysis Expression for Date range - Not working

Thu, 19 Apr 2012 14:13:53 GMT

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.

Set Analysis Expression for Date range - Not working

Thu, 19 Apr 2012 14:19:39 GMT

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

Set Analysis Expression for Date range - Not working

Thu, 19 Apr 2012 14:49:15 GMT

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?

Set Analysis Expression for Date range - Not working

Thu, 19 Apr 2012 14:55:27 GMT

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

Re: Set Analysis Expression for Date range - Not working

Thu, 19 Apr 2012 15:11:09 GMT

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.

Re: Set Analysis Expression for Date range - Not working

Fri, 20 Apr 2012 07:19:27 GMT

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....

Re: Set Analysis Expression for Date range - Not working

CELAMBARASAN — Fri, 20 Apr 2012 11:18:35 GMT

Hi,

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

Celambarasan

Re: Set Analysis Expression for Date range - Not working

Fri, 20 Apr 2012 11:31:16 GMT

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

Re: Set Analysis Expression for Date range - Not working

CELAMBARASAN — Fri, 20 Apr 2012 11:56:58 GMT

Hi,

Its in timestamp format.Check with this.

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

Celambarasan

Re: Set Analysis Expression for Date range - Not working

Fri, 20 Apr 2012 12:24:50 GMT

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

Set Analysis Expression for Date range - Not working

CELAMBARASAN — Fri, 20 Apr 2012 12:29:00 GMT

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

Re: Set Analysis Expression for Date range - Not working

Fri, 20 Apr 2012 12:33:40 GMT

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)

Re: Set Analysis Expression for Date range - Not working

CELAMBARASAN — Fri, 20 Apr 2012 12:39:58 GMT

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

Re: Set Analysis Expression for Date range - Not working

Fri, 20 Apr 2012 12:55:17 GMT

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?

Re: Set Analysis Expression for Date range - Not working

Fri, 20 Apr 2012 13:18:09 GMT

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?