Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Hi,
Its in timestamp format.Check with this.
=Sum({<DateCol ={">=$(=TimeStamp(vStartDate))<=$(=TimeStamp(vEndDate))"}>} Sales_Amount)
Celambarasan
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.
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
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?
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
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.
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....
Hi,
Did you checked the loaded DateCol field is in dateformat?if not you need to format on loading using Date#().
Celambarasan
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
Hi,
Its in timestamp format.Check with this.
=Sum({<DateCol ={">=$(=TimeStamp(vStartDate))<=$(=TimeStamp(vEndDate))"}>} Sales_Amount)
Celambarasan