Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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!
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?
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.
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?
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.
How would I get it to calculate the percentage automatically without having any manual effort?
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
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 |