topic Need help for filters Data between two dates in QlikView

Need help for filters Data between two dates

vishalgoud — Thu, 11 Jun 2015 10:53:50 GMT

Hi,

I have three date fields like Year,StartDate,EndDate.

Year calendar year which is used as a Filter.I have also few other fields like Agent and Sales and Sale year.

Now my requirement is like when i select a year in filter it should fall in between start and end dates of that selected agent and i have to show the data which is from the selected year to end year of that particular agent.

for ex.agent a have start date like 01/01/2012 and end date is like 10/16/2014.first i will select an agent and after that,

if i select year in filter it should fall between 01/01/2012 and 10/06/2014.

and the data which have to display should be from selected year to end year.

If i select 2013 in Year filter,The agent StartDate is 2012 and EndDate is 2014.I want to Display the agent sales from selected year 2013 to end year 2014.If the selected year is not in between the startDate and EndDate like 2011 then we should not show any data .

Tab1

LOAD * INLINE [
    ID, StartYear, EndYear
    1, 2011, 2015
    2, 2010, 2014
    3, 2009, 2013
    4, 2008, 2011
    5, 2012, 2014
    6, 2014, 2015
    7, 2007, 2010
    8, 2006, 2014
    9,2003, 2015
    10, 2005, 2010
    11, 2009, 2013
];

Tab2:
Load * Inline
[
ID,Sales,SaleYear
1,100,2011
1,150,2012
1,200,2013
1,250,2014

2,120,2012

3,130,2013

2,150,2014

2,190,2015

2,200,2013

3,180,2014

3,300,2015
];

LOAD StartYear as Year
Resident Tab1;

Please Help me It is an urgent requirement.I am unable to do this.

Re: Need help for filters Data between two dates

sushil353 — Thu, 11 Jun 2015 10:56:35 GMT

Hi,

Try using interval match function in qv.

HTH

sushil

Re: Need help for filters Data between two dates

vishalgoud — Thu, 11 Jun 2015 11:10:55 GMT

Hi Sushil kumar,

Please give me the Sample expression for this issue using intervalmatch

Re: Need help for filters Data between two dates

MayilVahanan — Thu, 11 Jun 2015 11:16:35 GMT

Try like this

Tab1:

Load * Inline

[

ID,Sales,SaleYear

1,100,2011

1,150,2012

1,200,2013

1,250,2014

2,120,2012

3,130,2013

2,150,2014

2,190,2015

2,200,2013

3,180,2014

3,300,2015

];

Tab2:

LOAD * INLINE

[

ID, StartYear, EndYear

1, 2011, 2015

2, 2010, 2014

3, 2009, 2013

4, 2008, 2011

5, 2012, 2014

6, 2014, 2015

7, 2007, 2010

8, 2006, 2014

9,2003, 2015

10, 2005, 2010

11, 2009, 2013

];

Inner Join

IntervalMatch(SaleYear, ID)

LOAD StartYear, EndYear, ID Resident Tab2;

Join(Tab1)

LOAD * Resident Tab2;

DROP Table Tab2;