Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Expression for Date range - Not working

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)

1 Solution

Accepted Solutions
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Its in timestamp format.Check with this.

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

Celambarasan

View solution in original post

15 Replies
Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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
Partner - Champion
Partner - Champion

Hi,

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

Celambarasan

Not applicable
Author

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
Partner - Champion
Partner - Champion

Hi,

     Its in timestamp format.Check with this.

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

Celambarasan