topic Re: 2 different dimensions in one graph in QlikView

one graph

Wed, 06 Aug 2014 02:26:33 GMT

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!

Re: 2 different dimensions in one graph

Anonymous — Wed, 06 Aug 2014 03:18:41 GMT

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.

Re: 2 different dimensions in one graph

sujeetsingh — Wed, 06 Aug 2014 03:43:10 GMT

Satya ,

have provided a way .

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

Re: 2 different dimensions in one graph

Wed, 06 Aug 2014 03:52:20 GMT

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.

Re: 2 different dimensions in one graph

Anonymous — Wed, 06 Aug 2014 04:08:38 GMT

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.

Re: 2 different dimensions in one graph

herbstwp — Wed, 06 Aug 2014 04:49:22 GMT

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].

Re: 2 different dimensions in one graph

Wed, 06 Aug 2014 14:17:43 GMT

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.*

Re: 2 different dimensions in one graph

Wed, 06 Aug 2014 17:56:52 GMT

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.

Re: Re: 2 different dimensions in one graph

Anonymous — Thu, 07 Aug 2014 07:07:09 GMT

See Attachment

Re: Re: 2 different dimensions in one graph

herbstwp — Thu, 07 Aug 2014 19:09:42 GMT

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

Re: 2 different dimensions in one graph

Fri, 08 Aug 2014 12:34:04 GMT

Satyadev,

Thank you so much for your help!

That is exactly what I wanted. You saved my weekend!

I hope I can treat you a lunch if you live in my town or close. I really owe you, Satyadev.

Thank you again!