Dynamic Quarter Quota Column

[mskusace]

I am generating an app in Qlik Sense which reports information about certification for employees. I have created a macro within Excel to do all the data processing automatically.

One of the metrics captured is the total count of certifications achieved. For each quarter over a 2 year span, there is a different quota for this metric.

The required number of certifications per year per quarter is below.

• Y1 Q1 - 5
• Y1 Q2 - 10
• Y1 Q3 - 20
• Y1 Q4 - 40
• Y2 Q1 - 45
• Y2 Q2 - 50
• Y2 Q3 - 60
• Y2 Q4 - 80
• Y3 Q1 - 5
• Y3 Q2 - 10
• Y3 Q3 - 20
• Y3 Q4 - 40
• Y4 Q1 - 45
• Y4 Q2 - 50
• Y4 Q3 - 60
• Y4 Q4 - 80

The raw data contains the total number of certifications achieved. I want to create a dynamic column that calculates the percentage of people who have obtained the quota for that specific year and quarter for when the report is pulled. I would like for the column to be named "% with 5+ Certifications", "% with 10+ Certifications", "% with 20+ Certifications", etc. to correspond to the quota for the current year and quarter. The idea is when the quarter changes, the metric automatically changes to measure against the new quota for the new quarter. The report/dashboard should only show the metric measured against the current quarter. 

Is this possible to accomplish whether in Qlik Sense or any other way? Any guidance on how to accomplish would be greatly appreciated. Please let me know if you need any clarification.

[sunny_talwar]

Not entirely sure I understand... based on the data you provided above, what do you expect to see as an output?

[mskusace]

I have a table with the various metrics broken down by department. Currently, there are 2 departments. It shows the total number of employees, some other metrics, and then it needs to show the number of certifications completed compared against the quota for that quarter, which will be a %. 

If it's Y1 Q1, with 100 employees, and 10 of them have completed 5 or more certifications, it will show 10% under a column called "% with 5+ Certifications".

If it's Y1 Q3 with 100 employees, and 50 of them have completed 20 or more certifications, it will show 50% under a column called "% with 20+ Certifications", which will replace the column "% with 5+ Certifications" because it is Y1 Q3.

Does that help clarify?

[sunny_talwar]

So, at any given point of time, you will only have a single column? Either "% with 5+ Certifications" or "% with 20+ Certifications"? Or are you saying that each Quarter and Year will have a one column? I am unable to understand that will happen to "% with 5+ Certifications" when you show "% with 20+ Certifications"? Does it disappear? Also on what basis will it disappear based on the current year quarter?

[mskusace]

Correct in that I will only have a single column, which will be dependent on the year and quarter the report is pulled. It is pulled on a monthly basis right now. That column title and quota will updated depending on the current year and quarter the report is pulled. 

[sunny_talwar]

So, this should be doable in Qlik Sense now. You can use a calculated label for your column name. Have you tried that already or have you not heard about it before?

[mskusace]

I have heard about it, but I haven't used it. I also wasn't sure how to get the actual column values to update to measure against the new quota for the year and quota. I currently have the date the report was pulled and I can access that field. I am using the fiscal year and quarters for the fiscal year. 

Below is the quota for certifications for each quarter and the years and months included in each quarter. I need it dynamic enough to automatically update and measure the total number of certifications per department against the quota for that time to calculate a percent. I want to see the percent of people that have met the quota per department.

• Q1 - 5 - 2018 Oct, Nov, Dec
• Q2 - 10 - 2019 Jan, Feb, Mar
• Q3 - 20 - 2019 Apr, May, Jun
• Q4 - 40 - 2019 Jul, Aug, Sep
• Q1 - 45 - 2019 Oct, Nov Dec
• Q2 - 50 - 2020 Jan Feb, Mar
• Q3 - 60 - 2020 Apr, May, Jun
• Q4 - 80 - 2020 Jul, Aug, Sep
• Q1 - 5 - 2020 Oct, Nov Dec
• Q2 - 10 - 2021 Jan, Feb, Mar
• Q3 - 20 - 2021 Apr, May, Jun
• Q4 - 40 - 2021 Jul, Aug Sep

How would I get it to calculate the percentage automatically without having any manual effort?

[sunny_talwar]

This is how your data looks like?

This doesn't look like how the data would be... can you share how the raw data looks like... preferably share few rows of data which we can load into an app to show how it can be done. Without knowing the data, it would be very difficult to guide you in the right direction

[mskusace]

The raw data looks like the table below. I truncated many of the columns, but just to give you an idea.

Assume report was pulled 2018-12-14 so Year 1 and Quarter 1.

Person	Department	Certifications	Updated Plan
Monkey	Dep 1	4	Yes
Zebra	Dep 2	7	No
Dog	Dep 1	13	No

[sunny_talwar]

So, based on this data... you would see "% with 4+ Certifications"? or something else? and what is the percentage you expect to see 1/3 = 33.33%?