Problem?? Drawing Date(range)/ Gender Chart.

[#1]

Hello Everyone,

I am facing problem in generating a chart. I have taken an excel sheet and I have a "hire date" column and a Gender field in it. Now I have to calculate like between Date 1 and Date 2 how many males and females are there in company. so i am using the following script:

Dimension: Gender

Expression: 1) if([Hire Date]>='10/27/2010'  and [Hire Date]<='09/25/2011', Count(Dept#))

2) if([Hire Date]>='10/28/2009' and [Hire Date]<='09/26/2010', Count([Dept #]))

So I am expecting this to work as in based on Male or Female: it will count how many hired between the dates mentioned in expression 1 and same for expression 2.

I am kinda confused as I am not gthe output. Please correct me where I am wrong.

Thank you

Yaman

[johnw · Accepted solution]

The if() goes inside the count(), not the other way around, and as written, your dates aren't dates but simply strings of text that QlikView can't compare to dates.  For that matter, the if() should also be replaced with set analysis for performance, and you should reference a "fire date" field as well.  And for that matter you shouldn't be using hardcoded dates in a real application, and you might want, say, a line chart showing the change in the number of people in the company over time, which would be solved by intervalmatching a calendar to the hire and fire dates so that you have one record for every day the person was working for the company.  But let's start at the very beginning, and at least get your count(if(...)) working with your specified conditions.

count(if([Hire Date]>=makedate(2010,10,27) and [Hire Date]<=makedate(2011,9,25),Dept#))

Also, why are you counting Dept#?  Seems like you should have a Person# or some such, and count that.  You might get the right count by counting just about any field in the table, but you really should count what you want to count instead of random fields from the table.

[#3]

Hello John,

You are correct my statement worked but this is not what I wanted to display, its displaying something else... I mean I see the two expressions ,.ie, dates are actually FY 2009 and FY 2010. now I want to display like :

1) 2010: count(if([Hire Date]>='10/27/2010'  and [Hire Date]<='09/25/2011',(Dept#))----> x axis

i want to display male and female for 2009 , ie, between above dates--> stack chart showing total M and F.

2) 2009: count(if([Hire Date]>='10/28/2009' and [Hire Date]<='09/26/2010', [Dept #]))

the same distribution of male and female for FY 2009 as well.

But what I am getting is 2 bars for male and female..based on my expresions. I know there is a problem in my approach and I have no clue how to go about it??

Also, you correct I want to display the total new hires(showing trend on a graph) the company have for every year starting friom 2003 to 2010 and then based on that , i further want to divide them based on other factors like gender, race etc.

so a dashboard which can tell you that we have 1000 new hires in 2004 and it rose to 1500 in 2005 and so on. Also it can show you the trend of how many males and females are there in different years etc.

"Set Analysis Performance" , i am not sure what you meant by that? As i recently started working on QV so...

Also I dont think I have a fire date field as of now...so correct me if i am wrong, according to you I should create a calender and link my hire date to that and then further use my calender to show trends?

Also can you share any file where I can see how to design a calender and how to link our fields to that?

I am sorry for too many questions.

Thank you so much for your help. I really appreciate that.

Best,

Yaman

[johnw]

OK, if I get time today, I'm going to throw together an example of how I'd solve this sort of problem, and then give a little explanation of why I've done it the way I've done it.  Doing it in what I consider the right way is far from simple or obvious (an unfortunate QlikView shortcoming in my opinion), so not something I'd expect you to really figure out from me just chattering away about things like "set analysis for performance" and "intervalmatch". 

[#5]

Thank you so much..

[johnw]

Heh, I was well into creating the example when I realized your problem is much easier than I thought.  I thought you wanted to know the number of employees that worked for the company at some point during the fiscal year.  Instead, you just want how many employees were hired.  So yes, you only need a hire date, not a fire date.  And no, you don't need to generate all the dates the employee worked for the company.  That's to answer a question you didn't ask.

On the other hand, if I were doing this for a real application, I'd very likely take the more complicated route, and just add a "Hired?" flag to my linkage table.  That should support answering a lot more questions that I think are likely to be asked, such as the one I mentioned, how many employees were working for the company at some point during each fiscal year.  So I think I'm going to go ahead and complete and post that example, at least as the first salvo.

See attached. 

If I have time, and you have interest, I'll create an example without a fire date or the table that sits between the main data and the calendar that can answer your specific question.  But really, you might be able to work it out yourself with a couple hints.  Basically, remove that middle table, and replace "Date" in the calendar load with "Hire Date".  The first chart will then become your count of hired employees, and the other two charts will stop working.  But again, in a real application where I'd expect to have to answer more complicated questions about employees, I'd probably generate that middle table.

[#7]

Hey John

Thank you so much for your response. Well I am gonna spend my whole day today working on the file...u send me and implementing it. Lets see how far I am able to resolve my problem.

Thank you again. you have a good day.

Yaman