Date Range Set Expression not working

Fri, 15 Aug 2014 12:05:34 GMT

Hello

I am trying to use a set expression in my QlikVIew document to calculate the sales within a date range.

I am calculating the sales amount based on what date the user selects and the beginning date of that date.

So if the user selects 14/08/2014 then the beginning date of the month of that date would 01/08/2014.

My aim is to calculate the sales between the first date of the month (01/08/2014) and the date selected (04/08/2014)

I have created two variables:

1. vThisMonthStart. This carries out the expression MONTHSTART(DATE(SALEDATE))

2. vMaxSalesDate: This carries out the expression MAX(DATE(SALESDATE))

These variables were created in document and not in script.

My date range Expression is as follows:

sum({<SALESDATE={'=DATE(vThisMonthStart)<=DATE(vMaxSalesDate)'} >} SALES)

The above expression is used in a table or pivot table.

Normally when I select the date range from a list box, I get the correct sum of sales when I verify them from a database.

So I selected 01/08/2014 TO 13/08/2014 and the total sales was correct and verified.

I am expecting to just select one date (13/08/2014) and my expression should show the sales from 01/08/2014 TO 13/08/2014.

I am not getting this result rather I am getting just the sales for that day (13/08/2014)

What am I doing wrong in my set expression?

How can I fix this to get the result I want?

Thanks

Re: Date Range Set Expression not working

giakoum — Fri, 15 Aug 2014 12:14:39 GMT

You need to ley QV know that it needs to calculate by using the $ sign :

sum({<SALESDATE={'>=$(=DATE(vThisMonthStart, 'YYYY-MM-DD'))<=$(=DATE(vMaxSalesDate, 'YYYY-MM-DD'))'} >} SALES)

hope I got the parenthesis right!

Fri, 15 Aug 2014 12:25:52 GMT

This worked fine.

You do not need the date format in the DATE function. Just the DATE function itself.

Thank you.

giakoum — Fri, 15 Aug 2014 12:32:02 GMT

true, if you did a good job in the data modelling. But I was just making sure