topic Date Dimension filtered by variables in QlikView

Date Dimension filtered by variables

billuran — Wed, 25 Nov 2020 16:16:04 GMT

Hello, I have 2 date ranger pickers to set the start and end date as variables.

I have an expression that uses these variables to filter the data.

sum({<[Date1] = {'>=$(=Date(v_DateFilterStart))<=$(=Date(v_DateFilterEnd))'} >} CALCVOLUME)

The need: I want to create a line graph that has a Date1 dimension and the above expression. But have the Date1 dimension be filtered by what is selected from the date pickers.

Any help on how to get this dimension to reflect the variables.

Re: Date Dimension filtered by variables

sunny_talwar — Wed, 25 Apr 2018 19:19:45 GMT

What do you get when you use this in a text box object?

='>=' & Date(v_DateFilterStart) & '<=' & Date(v_DateFilterEnd)

Does this give you the correct date range (based on the selection) and the format which matchs Date1's format?

Re: Date Dimension filtered by variables

billuran — Wed, 25 Apr 2018 19:22:25 GMT

Yes it shows >=01/01/2017<=3/11/2018

Re: Date Dimension filtered by variables

sunny_talwar — Wed, 25 Apr 2018 19:24:55 GMT

Is this the same format as the Date1... also does it give >=01/01/2017<=03/11/2018 or >=1/1/2017<=3/11/2018.... essentially, is it M/D/YYYY or MM/DD/YYYY? And what is the format of Date1?

Re: Date Dimension filtered by variables

billuran — Wed, 25 Apr 2018 19:30:23 GMT

M/D/YYYY sorry. And Date1 is MM/DD/YYYY

Re: Date Dimension filtered by variables

sunny_talwar — Wed, 25 Apr 2018 19:32:39 GMT

Then try this

Sum({<[Date1] = {">=$(=Date(v_DateFilterStart, 'MM/DD/YYYY'))<=$(=Date(v_DateFilterEnd, 'MM/DD/YYYY'))"}>} CALCVOLUME)

Note: Make sure to use Double quotes instead of single quote.....