Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
CHansford
Luminary
Luminary

Complient Objectives % calculation over various period and location dimensions

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

1 Solution

Accepted Solutions
TimvB
Creator II
Creator II

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.

View solution in original post

2 Replies
TimvB
Creator II
Creator II

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.

View solution in original post

CHansford
Luminary
Luminary
Author

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