Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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!
Hi
This worked fine.
You do not need the date format in the DATE function. Just the DATE function itself.
Thank you.
true, if you did a good job in the data modelling. But I was just making sure