Help!!

SahilArora3139

can someone assist in building a robust data analytics logic to monitor and highlight the defaulters?

We have a dataset containing employee names, hours of completed training, and pro rated or year end training hours.

The pro rated hours represent the desired annual training target defaulted to 25 hours.

However if an employee joins midway the target is pro-rated accordingly.

The objective is to develop a logic to identify defaulters on quarterly basis,

Q1: 25% of pro rated should be completed

Q2: the other 25%

Q3: 25%

Q4:25 %

I need to segregate the workflow quarter wise ensuring that only the results or each specific quarter are highlighted during analysis.

If someone can help me , it would be highly appreciated and Thank you in advance.

Data Set.xlsx

Workflow

Developer Tools

Preparation

Accepted answers

All comments

Qiu

@SahilArora3139
I can not see any Date Related data in your input.
Is that intentional? maybe it is better you can provide the desired sample output as well.

SahilArora3139

the data is not date related.

its just that i have two columns one is the hours done by each member and pro-rated which is the total hours once needs to do by end of year.

so my output is that:

i want to convert my pro rated column into 4 parts

example: total pro rated by default is 25hours

6.25

Tricky part is if pro rated is 16 for a new joinee i have to dynamic create a formula to segregate pro rated into 4 quarters 25% of pro rated.

and lastly i want to compare tht as per the q1 target has person fulfilled the hours by looking into total hours column.

SahilArora3139

Objective 1: Like this so q1 q2 q3 q4 is generated through formula which is 25% of pro rated hours.

objective 2:

quarter 1 when i am running the workflow only q1 will be compared to total hours

example if 25 is pro rated hours

and q1 is 6.25 (25% of pro rated which is 25)

then person who has not done 6.25 will be flagged others will not be included. Rather be not visible if possible.

and then the same will be for q2 when in quarter2 i want to run the workflow only results for q2 should be there.

IMG_8536.jpeg

BS_THE_ANALYST

@SahilArora3139 based on your request, I'd just use a formula tool and create 4 columns called q1, q2, q3, q4. Each of these quarters are a replica of one another using this formula:

[pro rated column hours]/4

Strange not to have a date of joining column here. I guess we can infer this from the prorated column.

If you want to highlight "defaulters" you've got a multiple options.

Filter tool with logic as: [Total Hours Training] < [Pro Rated Column Hours].
1. Create an output file for defaulters on the True anchor of the filter as they are defaulters.
2. Leverage the email tool on the True anchor of the filter to get notified via email with/without attachments
Use the Table Tool in the reporting tool palette to create conditional formatting on the names of the colleagues. This will auto generate an excel file highlighting people who are defaulters. Then you can filter by colour in your excel output.

All the best,

Ben

SahilArora3139

Thank you for the help

but here if i use filter tool:

Total Hours Training] < [Pro Rated Column Hours]

it will not work because by default its 25 but if someone joins in between it will not be 25 so it should be dynamic

for example if someone has pro rated 19.58 for the year the Q1 will be 4.8 not 6.25 which is common for 25 training hours

BS_THE_ANALYST

@SahilArora3139 how are we meant to be able to determine if someone has joined in between without providing dates? Please talk me through this.

All the best,

Ben

SahilArora3139

For that there is a vlookup through which this column is updated automatically as per the master tab.

BS_THE_ANALYST

@SahilArora3139 in the meantime, here's a skeleton workflow to build things from:

New Workflow1.yxzp

BS_THE_ANALYST

"For that there is a vlookup through which this column is updated automatically as per the master tab."

You will need to read that tab into Alteryx and do the "vlookup" in Alteryx using a Join Tool. You'll probably need to join on Employee ID.

Can you give an example of what you'd expect if someone joined the business 2 months into the financial year? How you'd want that output to look for them if you did a manual calculation

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703