7 Replies Latest reply on Jan 22, 2019 2:17 PM by Lesley Newell

    Point System for Certificates

    Lesley Newell

      I am trying to create calculations for a point system which awards certificates depending upon the amount of points an individual receives.

      -To receive a certificate, individuals must earn 50,000 points.

      ----5,000 Gold or Platinum points can be earned for participation in each workshop and/or for teaching observations 

      ----2,500 Green points are awarded for homework assignments. There is a sample data set attached, and an example scenario for a Level 1 Gold Certificate would be something like the following:

       

      SCENARIO 1: Qualifications for a Gold Certificate :

      -50k total points required

      ----at least 35K gold points from any workshop

      ----remaining 15k points = green or gold points (pull from green first, then look to gold)

       

       

      Update:

      I have the workbook attached.

       

      On the "Gold Certificate" tab, I have it filtered to the names that would qualify for the certificate as described in scenario 1 above. Is there a calculation that I should use instead of filtering the points, so that I can exclude points once an individual has been awarded a certificate? Once they get a gold certificate, they may work towards a platinum certificate, and would not be able to use the same workshop points for both certificates.

        • 1. Re: Point System for Certificates
          Deepak Rai

          Hi Lesley,

          Please create Something in Tableau and Let us know where you are getting Stuck. People would help you happily then.

          Thanks

          Deepak

          • 2. Re: Point System for Certificates
            Lesley Newell

            Thank you for your response. I have attached the workbook and additional information at the bottom of my post.

            • 3. Re: Point System for Certificates
              Don Wise

              Hi Lesley,

               

              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

              • 4. Re: Point System for Certificates
                Lesley Newell

                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.

                • 5. Re: Point System for Certificates
                  Don Wise

                  Hi Lesley,

                   

                  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.

                   

                  Thanks, Don

                  • 6. Re: Point System for Certificates
                    Peter Fakan

                    Hi Lesley,

                     

                    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.

                     

                    HTH

                     

                    Peter

                    • 7. Re: Point System for Certificates
                      Lesley Newell

                      Peter -

                       

                      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.