topic Re: Dynamic Quarter Quota Column in App Development

Dynamic Quarter Quota Column

mskusace — Wed, 02 Jan 2019 15:44:29 GMT

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.

Re: Dynamic Quarter Quota Column

sunny_talwar — Wed, 02 Jan 2019 15:55:35 GMT

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

Re: Dynamic Quarter Quota Column

mskusace — Wed, 02 Jan 2019 16:14:09 GMT

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?

Re: Dynamic Quarter Quota Column

sunny_talwar — Wed, 02 Jan 2019 16:23:59 GMT

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?

Re: Dynamic Quarter Quota Column

mskusace — Wed, 02 Jan 2019 16:27:02 GMT

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.

Re: Dynamic Quarter Quota Column

sunny_talwar — Wed, 02 Jan 2019 16:30:12 GMT

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?

Re: Dynamic Quarter Quota Column

mskusace — Wed, 02 Jan 2019 16:39:31 GMT

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?

Re: Dynamic Quarter Quota Column

sunny_talwar — Wed, 02 Jan 2019 16:43:21 GMT

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

Re: Dynamic Quarter Quota Column

mskusace — Wed, 02 Jan 2019 16:55:57 GMT

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

Re: Dynamic Quarter Quota Column

sunny_talwar — Wed, 02 Jan 2019 17:25:28 GMT

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%?

Re: Dynamic Quarter Quota Column

mskusace — Wed, 02 Jan 2019 17:32:23 GMT

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.

Re: Dynamic Quarter Quota Column

sunny_talwar — Wed, 02 Jan 2019 17:43:38 GMT

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

Re: Dynamic Quarter Quota Column

mskusace — Wed, 02 Jan 2019 18:11:07 GMT

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?

Re: Dynamic Quarter Quota Column

sunny_talwar — Wed, 02 Jan 2019 18:18:12 GMT

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.

Re: Dynamic Quarter Quota Column

mskusace — Wed, 02 Jan 2019 19:21:47 GMT

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.

Re: Dynamic Quarter Quota Column

mskusace — Fri, 04 Jan 2019 20:23:06 GMT

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!