Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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
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.
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.