topic Re: employee count varies with age group (emp count shows different in two tables) in QlikView

varies from type

Anonymous — Fri, 04 Dec 2015 15:33:12 GMT

I have two tables

please please request help

attached is the sample qvw.

Re: employee count varies with age group (emp count shows different in two tables)

Anonymous — Fri, 04 Dec 2015 16:20:41 GMT

any kind of support

Re: employee count varies with age group (emp count shows different in two tables)

sunny_talwar — Fri, 04 Dec 2015 16:29:59 GMT

You have made certain selections in your app already. the right chart shows 74 for Contract when I have Employment Type CONTRACT selected,

but shows 72 when I select PERMANENT

I think there is some bigger data model issue or something related to your expression that isn't working the way you want it to work. I think we will need to get to the very basics and know if 74 is actually the right number and how exactly you came up with it ( What was the logic)?

Re: employee count varies with age group (emp count shows different in two tables)

Anonymous — Fri, 04 Dec 2015 16:48:44 GMT

Hello Sunny

Thanks for the analysis

the expression is only the requirement

and the logic is no of employees joined year , and the age wise but the employees age wise and the ytd employees should be the same but some where we need to use sort key

but not sure how to use it though

pls help me on this i know till this requirment i dnt know much i need the figure to be 74 that matches to the other chart

Re: employee count varies with age group (emp count shows different in two tables)

omerfaruk — Fri, 04 Dec 2015 16:57:53 GMT

Your aggr function produces 3 values. 1 is unknown, is this the desired result ? I advise you to revise it once again.

Another thing,

Based on your calendar script, in year 2015,there are

280 employees,

106 contractors,

187 Permanent employee.

Contractors and permanent sum is not equal to total employees. 293 - 280.

Is there any duplicate somewhere in your data ?

Re: employee count varies with age group (emp count shows different in two tables)

Anonymous — Fri, 04 Dec 2015 18:09:47 GMT

Thanks Omer Demir for your analysis too.

the same thing i was also trying to figure out ...

but i hope there is no distinct values and need to customize the expression some how

i was finidng it way difficult to match these two tables contract emp numbers

need help even i am also working out on expression .

could you look into [(h)Sort Key] i.e max([(h)Sort Key]) which gives the latest record or date for the employees

i am trying to apply the sort key logic in the expression but of no use

Re: employee count varies with age group (emp count shows different in two tables)

Anonymous — Fri, 04 Dec 2015 18:40:44 GMT

any other suggestions?

Re: employee count varies with age group (emp count shows different in two tables)

sfatoux72 — Fri, 04 Dec 2015 18:50:14 GMT

Hi John,

On table "YTD No. of Employees by Employment Type as at 2015" you use a calculated dimension that you named "Staff Employment Type", but in reality it is "Last Staff Employment Type".

The other table depends of the selection of "Staff Employment Type". But during the year people of Staff could have different "Staff Employment Type".

A good solution will be to create a dimension "Last Staff Employment Type" during the data load and use it instead :

- the dimension field "Staff Employment Type"

- the calculated dimension on table "YTD No. of Employees by Employment Type as at 2015"

Regards

Re: employee count varies with age group (emp count shows different in two tables)

Anonymous — Sat, 05 Dec 2015 05:06:40 GMT

Hello Sebastien

thanks alot for your analysis

confused a bit and could you please share me sample expression to write or app ?

could you please respond?

Re: employee count varies with age group (emp count shows different in two tables)

Anonymous — Sat, 05 Dec 2015 17:27:46 GMT

Hello any suggestions

Re: employee count varies with age group (emp count shows different in two tables)

omerfaruk — Sat, 05 Dec 2015 18:52:00 GMT

Hi John,

What I can suggest is to try to produce the required value by making listbox/multibox selections. With this you can see the actual result you would get when you write an expression. Unless your data model is bad, this should work. I hope you find a way out from this issue.

Re: employee count varies with age group (emp count shows different in two tables)

Anonymous — Sun, 06 Dec 2015 11:39:12 GMT

Hello any suggestions would be great

still finding no solution

Re: employee count varies with age group (emp count shows different in two tables)

sfatoux72 — Sun, 06 Dec 2015 13:04:42 GMT

Hi John,

To be more clear, when you start to create complex calculated dimension that signify that your data model is not appropriate to your need. In this case you must create this dimension on your data model.

You could find in attachment an example where I create the dimension "Last Staff Employment Type"

I just do it for 201512, you will need to create a loop to do it for each month and see how to integrate this on your data model)

I add a list box with the new dimension and I modify the graph to use the new dimension instead the Aggr. And you have the result that you want.

Another time, my example work only the selection Year=2015, Quarter= and Month=.

I found 73 Contract and 180 Permanent.

With the new dimension "Last Staff Employment Type", you could simplify dimension and expression (see orange object).

Warning: when you use alternate state, if you use $ in set analysis, you reference the default state. Specify the state that you want to reference or nothing to inherit the object state.

Re: employee count varies with age group (emp count shows different in two tables)

Anonymous — Mon, 07 Dec 2015 10:33:39 GMT

Thanks a ton Sebastien

I will look into it