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.

[mskusace]

The report would look like the table below. Since it is Year 1 Quarter , it is 5+ certifications. If it was Year 1 Quarter 3, it would be 20+ certifications.

Department	% with 5+ Certifications	% with Updated Plans
Dep 1	50%	50%
Dep 2	100%	0%

Thank you for taking the time to help me also, I appreciate it.

[sunny_talwar]

Try something like this

Dimension

Department

Expression 1

=Count(DISTINCT If(Certifications > If(Month(Today()) < 4, 5,
	If(Month(Today()) < 7, 10,
    	If(Month(Today()) < 10, 20, 40))), Person))/Count(DISTINCT Person)

Label for Expression 1

=Count(DISTINCT If(Certifications > If(Month(Today()) < 4, 5,
	If(Month(Today()) < 7, 10,
    	If(Month(Today()) < 10, 20, 40))), Person))/Count(DISTINCT Person)

Expression 2

=Count(DISTINCT {<[Updated Plan] = {'Yes'}>} Person)/Count(DISTINCT Person)

Label for Expression 2

% with Updated Plans

[mskusace]

So that hard codes the quarters by the month value. However, I am going by fiscal year so the table I posted earlier with the months included in each quarter contains the actual quarters. How would I factor in Year 1 and Year 2 in this also? I was thinking of a similar solution, but I couldn't think of an automated way to capture that for Year 2 and beyond.

Also, the Label 1 expression is the same as the Expression 1. Will that produce the proper label?

[sunny_talwar]

I am not complete sure I am following all the things you are saying. I am somewhat lost. It would make it much easier if you can share your app so that I can see what you have and what you might be going for. Right now, I am just throwing the balls in the dark.

[mskusace]

I have 3 sample data sets and 3 reports for each of those data sets. Key things to keep in mind is that the raw data is pulled monthly and will fall into a certain quarter. It might be better for us to keep a counter of 8 to represent the 8 quarters since it resets after that. I am not sure, but I was hoping for just a way using the date the report was pulled.

Notice the column name changes in the reports to match which year and quarter it is in, or which cycle it is in during that 2 year period. Please refer to the table I created showing the quota for each quarter based on the year.

Y1 Q1

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

Y2 Q2

Person	Department	Certifications	Updated Plan
Monkey	Dep 1	50	Yes
Zebra	Dep 2	30	Yes
Dog	Dep 1	50	No

Y2 Q4

Person	Department	Certifications	Updated Plan
Monkey	Dep 1	80	Yes
Zebra	Dep 2	100	Yes
Dog	Dep 1	79	Yes

Report for Y1 Q1

Department	% with 5+ Certifications	% with Updated Plans
Dep 1	50%	50%
Dep 2	100%	0%

Report for Y2 Q2

Department	% with 50+ Certifications	% with Updated Plans
Dep 1	100%	50%
Dep 2	0%	100%

Report for Y2 Q4

Department	% with 80+ Certifications	% with Updated Plans
Dep 1	50%	100%
Dep 2	100%	100%

I hope this clarifies any confusion. Unfortunately, I can't share the app, but the logic on the sample data will be the same. Please let me know if you need anything else!

Thank you for your patience.

[mskusace · Accepted solution]

I figured out a solution. If anyone is interested, I have posted it below.

I did everything outside of Qlik Sense. Since I was capturing the date the reports were pulled, I used that to capture the quarter and the year. Since it resets every 2 years, I created a separate table from year 2018-2051 (2018 = 1, 2019 = 2, 2020 = 1, 2021 = 2, etc.). Then, I parsed the date apart and set up an IF statement to determine which quarter, since it is fiscal year and it is different than a calendar year. Lastly, I set up an IF statement to determine which cycle (quarter * year, which ranges from 1 - 😎 and to use the proper quota for that quarter (5, 10, 20, 40, 45, 50, 60, and 80). 

Then within Qlik Sense, I can use the same rules to determine which quarter in the 2 year cycle (quarter * year) and to dynamically name the column and do all the calculations. If anyone would like further clarification, I would be happy to go more in depth!

Thanks for taking the time to reply!