topic Help with merge the dates in QlikView

Help with merge the dates

Wed, 21 Mar 2012 17:51:46 GMT

I have the following scenario

Table1:LOAD * INLINE[ID,Start_Date, End_Date, City
01,1/1/2010,1/5/2011,001
02,1/4/2010,1/12/9999,002
03,1/2/2010,1/12/9999,002
04,1/5/2010,1/12/9999,004
05,1/2/2010,1/5/2011,001
06,1/5/2010,1/2/2011,002
07,1/7/2010,1/3/2011,003
08,1/8/2010,1/3/2011,001
09,1/3/2010,1/5/2011,003
10,1/10/2010,1/5/2011,004

11,1/7/2010,1/3/2011,001
12,1/10/2010,1/3/2011,002
13,1/3/2010,1/5/2011,003

14,1/8/2010,1/2/2011,003

15,1/8/2010,1/3/2011,003];

I want to create a Chart that show me how many are active and how many are inactive by month or year,

For example I select a Month and the Chart show me tow bars

5- __

3- | | ___

1- | | | |

____|__|__|__|____

(a) (b) Month

(a) Active (b) Inactive

My problem is in the dimension; I need merge the dates or do something so I can only see the Active and Inactive by month or year

I will appreciate you help

thanks

Re: Help with merge the dates

Wed, 21 Mar 2012 21:26:49 GMT

If I am understanding your problem correctly then I believe the following should help:

In order to select a month and year I created another inline table:

I used your first table and then added to the script

Dates:

LOAD * INLINE

[

MONTH, YEAR

1, 2010

1, 2011

2, 2010

2, 2011

3, 2010

3, 2011

4, 2010

4, 2011

5, 2010

5, 2011

6, 2010

6, 2011

7, 2010

7, 2011

8, 2010

8, 2011

9, 2010

9, 2011

10, 2010

10, 2011

11, 2010

11, 2011

12, 2010

12, 2011

]

If you wanted to add more years then you could always triple up on each e.g.

12, 2010

12, 2011

12, 2012

Then reload the script.

Bring in the MONTH and YEAR fields

Now define a couple of variables:

Settings->Variable Overview...->Add

Make the variables

getMonth = GetFieldSelections(MONTH)

getYear = GetFieldSelections(YEAR)

newDate = MakeDate(getYear, getMonth)

Now create a new chart.

Add a calculated dimension with the following formula:

=IF( newDate >= Start_Date and newDate <= End_Date,'Active','Inactive')

Then define the expression as = COUNT(ID)

Now you will have a chart that, when a month and year are selected will show what I think you are looking for from the question.

Hope this helps!

I also have attached a sample qvw if you need extra reference.

Re: Help with merge the dates

Wed, 21 Mar 2012 21:32:02 GMT

Also similar to the above is create a calendar table.

Use an expression similar to this:

sum(aggr((if(CalendarMonthEnd>=[Hire Date] and CalendarMonthEnd <=if(Headstatus='A',monthend([Active Date]),MonthEnd([Inactive Date])),1,0)),CalendarMonthEnd,Employee ))//)

Active date i defined as if employee is active take today() * 2

Then you can create a chart by month and year and see who was active each month.