Discussion Board for collaboration on QlikView Layout & Visualizations.
I have the following scenario
Table1:LOAD * INLINE[ID,Start_Date, End_Date, City01,1/1/2010,1/5/2011,00102,1/4/2010,1/12/9999,00203,1/2/2010,1/12/9999,00204,1/5/2010,1/12/9999,00405,1/2/2010,1/5/2011,00106,1/5/2010,1/2/2011,00207,1/7/2010,1/3/2011,00308,1/8/2010,1/3/2011,00109,1/3/2010,1/5/2011,00310,1/10/2010,1/5/2011,004
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
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
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
LOAD * INLINE
If you wanted to add more years then you could always triple up on each e.g.
Then reload the script.
Bring in the MONTH and YEAR fields
Now define a couple of variables:
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.