Please create Something in Tableau and Let us know where you are getting Stuck. People would help you happily then.
Thank you for your response. I have attached the workbook and additional information at the bottom of my post.
What would constitute the number of points for a Platinum Certificate, after having achieved Gold?
Also, this would only be for one class term correct? No carry-over into additional terms? Thx, Don
The points do not expire.
Platinum Level Points are acquired by attending a workshop which is 10K gold pts and THEN upon submission & review of the Platinum homework or activity, the 10K gold is converted to the 10k platinum points (which are already defined in the data they are sending me. I do not work with this data but they asked IT if there was a better way as they have been manually tracking certification in excel - ah!)
The Platinum Certification requirements:
- CTL Gold Certificate + 50K points
- at least 35k = platinum points (of the 50k)
- remaining 15k = gold, green or platinum (pulling from green, then gold, then platinum EXCLUDING any previous used points that awarded the CTL Gold Certificate they should already have for this certification.
So it helps everyone with understanding, is there a reason the points have to come out of each pool as opposed to simply using the person's total points? That's where the complication will be in addition to the carryover.
Do you mean something like this ?
A couple of points;
- In your explanation you noted that the order of calculation should be green, then gold, then platinum - not that this makes a difference but the 'value' of the events appears to be the other way around - i.e. attending the course is a more valuable activity than the homework. Since this is an aggregation of points the order doesn't really matter in the calculated field though.
- Yes there probably is a better way to record manually tracking certification - My first inclination would be to simply calculate it out of the data, but at some point (whether it is the tutor at the end of the course or some other event) somebody will probably need to update the record to indicate that the student has completed/passed. Notwithstanding, having somebody manually confirm the points status is as good as any other method. At this stage given you have an established business process that is successfully capturing the data you need, I'd probably look at keeping it in place unless the business has tasked you to fix it.
Thank you. This does seem to solve some of the problem; however, you have to add the SUM function in front of every field in the calculation that you listed above or you get all "Null" values. As you probably know, this makes it an AGG field; therefore, causing problems whenever I try a boolean calculation, or another if/then statement for the remaining points. The error being can't mix aggregate and non-aggregate expressions.
- I think (but I could be wrong) the order of the points does matter when trying to find how many points remain for each type of point (green, gold, platinum) after a certificate is awarded. There are actually 5 different certificates (which is why I need the remaining points), but I figured if I gave the two most common scenarios, I would figure out the others since they are based on titles and groupings that I had to create in Tableau Prep and may not be in the sample dataset that I provided.
I've even tried pivoting the data by having "point" and "point type" (green, gold...) fields, but I still run into the same problems.
- Yes, they manually update changing gold to platinum and so forth, but asked that I find a way to automate who has which certificate.
The only other thing I can think of is manually creating sets once a certificate is awarded, and naming it the actual award date to exclude the "INs" of the set for the next certificate qualification, but I know that sets sometimes disappear if a data source is replaced or updated so that makes me nervous.
Is there any thoughts on what parameters can do in typing in the amount of gold points or green points to specify a certificate or something?
Thank you all so much for your responses and continued conversation.