Complient Objectives % calculation over various period and location dimensions

[CHansford]

Greetings very clever Qlik Community,

I am producing a number of visualisations, Straight Table and Combo Chart.  Here is the scenario, which has evaded me so far.

Org has 10000+ people and each month they report on the number of people who are complient and the trend from the previous monthly reports.

To be complient each person must have 4 valid objectives  (Objective>3)

To be valid the objective must be in the valid year ([Valid Year] =1)

and

Objective Status must be either Active, Completed or On hold ([Objective Status] =1)

I need to be able to calculate the Complient % at all levels. Org, Region, Area, Team and Individual.

Has anyone clever got a magic expression or script to achieve this?

Regards

Chris

[TimvB · Accepted solution]

Once an expression becomes complicated I would highly recommend to use the script as complicated expressions have negative effect on the front end performance. So, we need some scripting.

You state that you need the measure Complient (%) on many different dimensions. The dimension Individuals is of the most detailed level, as Teams, Area, Department, Region etc. all include Individuals. Thus, we need to create a flag if an Individual is Complient and add this field to the table containing the Individual information. 

I have no information about your data model / tables, but the script will look something like this:

Individuals_ComplientCount_map:
Mapping Load

%Individual_Key

count(Objective) as ObjectiveCount

Resident Table

Where [ValidYear] = 1 and [Objective Status] = 1
Group By %Individual_Key;

Individuals:

Load *,
If(ApplyMap(‘Individuals_ComplientCount_map’,%Individual_Key,0) > 3, ‘1’, ‘0’) as Individual_Complient_Flag

Resident IndividualsExtract;

So, now you know if an individual is complient. Thus, in the front end you can add a simple expression that calculates the Complient (%) for every dimension: sum(Individual_Complient_Flag) / Count(distinct %Individual_Key).

I hope this gave you some inspiration how to solve this.

[CHansford]

TimvanBenthem ,

Thanks for the alternative to what I've achieved in the meantime, yours may
well be a better solution and less disruptive to the front end.

I'll give it a whirl and let you know of the outcome.

Regards

Chris