one graph

[#1]

I have two graphs showing number of Employees (Up) and plan (down). Each graph is linked to different data bases (Employees month from "excel i" & plan per month from "excel ii")

I need to combine those two graphs into one. I thought it would be very easy since both dimensions are based on monthly basis. But it was not working properly. I put all dimensions and expression together, but the result gave me wrong numbers or showed no data.

How can I put those two graphs into one?

*Concatenate or any field name change should not be done*

Thank you!

[Anonymous]

So you have two separate tables which are not linked to each other and you don't want them to concatenate? Even you don't want field name to be changed?

Create calendar table which should have both manpower month and hire plan month to link both table and common calendar month to display in dimension of the chart.

[sujeetsingh]

Satya ,

have provided a way .

If you have association of the fields (dimensions ) then just use combo chart with line and bars.

[#4]

Thank you so much for your prompt response.

I should not change or concatenate the field name, because the field names are also being used by other graphs ( >15).

I can create calendar table within "excel I" file but not "excel ii," because it is directly coming from an SQL server source that I can never touch or edit. Is there any other way?

What if I add a field name "SERVICE_START_DATE" in excel i and give the same value of "Month"? Would that possibly link them?

Thank you.

[Anonymous]

Hi,

You don't have to create calendar in two separate table. Just create calendar table based on variable (see below), add two fields which can link to both tables and add common monthyear field which can be used in graph dimension.

LET vMIN_CALDATE = Num(MakeDate(2008,1,1));

LET vMAX_CALDATE = Num(MakeDate(2030,12,31));

Calendar:

LOAD

     Cal_DATE,

     Cal_DATE  as ManpowerMonth,

     Cal_DATE  as HirePlanMonth,

     MonthName(Cal_DATE) as MonthYear

;

LOAD Date($(vMIN_CALDATE) + RowNo() - 1) as Cal_DATE 

AutoGenerate 1 While $(vMIN_CALDATE)+IterNo()-1<= $(vMAX_CALDATE); 

If you are still facing any issue, provide sample apps with scrambled data to look into.

[herbstwp]

I would suggest using the IntervalMatch() funtion in your load script.

That should link each of your [Month] fields with the Employee data where [Month] >= [SERVICE_DATE_START] and [Month] <= [SERVICE_ACT_TERM_DATE].

[#7]

Satyadev,

Thank you for your help.

I have attached some sample files that I made based on similar situation.

"excel i" is what you can make some changes such as adding new field, except chaning the current names.

"excel ii" - please assume this file as SQL data coming from server. I can't do any change to this file.

"Test" is a qlikview file that includes dimensions and expressions that I am using.

I tried what you suggested me but it did not work properly.

Could you please show me how you would solve this problem?

Thank you again.

*The end reslut graph does not need to show after today's date.*

[#8]

Willem,

Thank you for your reply.

I am completely new to qlikview and have no clue how I can apply the intervalMatch function into my script. Can you please explain a bit more detail?

Thank you again.

[Anonymous · Accepted solution]

See Attachment

[herbstwp]

Okay, I've attached your sample app. I am assuming you want to show how many ID numbers are active during each of the planned months?

Cheers,

Willem