Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.