13 Replies Latest reply on Nov 14, 2014 2:12 PM by Matt Lutton

    Target with an added Percent

    Allison Pacek

      Hello,

      I was wondering if anyone has tried to do what I am trying to complete

       

      I am being asked to take our customer counts for FY14 (June 2013- May 2014) and get a target based on 10% higher than FY14's customer count. This is to determine if our sales people have grown their customer count by 10% for this Fiscal year. Does anyone know how to do this? ( I want to actually to show the numbers to show their target).

      Thanks

       

      Message was edited by: Allison Reif I added an attachment. I could replace FY2014 with FY2015 Target. It should be FY2014 customer count *1.1 the calculated fields "Fiscal Year" and "FY15 Target" are my attempt at doing this. (failed)

        • 1. Re: Target with an added Percent
          Matt Lutton

          Can you post a sample TWBX file with some data that matches the structure you are working with?  You could copy and paste a small subset of your actual records into Excel, remove and randomize fields, as needed, and use that small subset of records to demonstrate what you're after in this thread.  Be sure to include a mock up of what you'd like to see in Tableau, as well, as this helps eliminate any confusion.

           

          Cheers!

          • 2. Re: Target with an added Percent
            Allison Pacek

            i added a workbook.  Along with these notes:

            I could replace FY2014 with FY2015 Target. It should be FY2014 customer count *1.1 the calculated fields "Fiscal Year" and "FY15 Target" are my attempt at doing this. (failed)

             

            Thanks

            • 3. Re: Target with an added Percent
              Matt Lutton

              Can you provide a mock-up of what you'd like to see in a view in Tableau?  I just want to be sure I understand your intended goal here.

              • 4. Re: Target with an added Percent
                Matt Lutton

                If you create a custom date field to represent the Year values in your "Invoice Date" field, this calc should provide you with what you want:

                 

                IF attr([INVOICE_DATE (Years)]) == #1/1/2014#

                THEN ([Customer Count]*1.1) else [Customer Count]

                END

                 

                Version 8.2 TWBX attached.  I've included the Customer Counts, and the new "Customer Counts with Target" field, so you can see the difference.  You could take this a step further, and parameterize the Percentage value you want to use for the Target.

                 

                Targets.png

                • 5. Re: Target with an added Percent
                  Allison Pacek

                  I hope this helps. i made it in Excel

                     

                  FY 15 Customer CountFY14 Customer CountFY15 Target
                  Territory 1101011
                  Territory 2152022
                  Territory 3203033
                  Territory 4254044
                  Territory 5305055
                  Territory 6356066
                  Territory 7407077

                   

                  Managers would be able to tell based on FY15 Target if their FY15 customer count is above or below that target (all my examples show below target)

                  • 6. Re: Target with an added Percent
                    Matt Lutton

                    See my other reply above, and let me know if that gets you where you need to be.  When I asked for a mockup, I meant for the actual data you've provided in Tableau -- I understand what you're after, but I'm not sure how you want the view arranged or set up.

                     

                    Cheers

                    • 7. Re: Target with an added Percent
                      Allison Pacek

                      This almost does it. I want to have the target be based on the previous year.

                      FY14 Customer Count*1.1 = FY15 Target

                      Note: Our fiscal year is not calendar (6/1 to 5/31)

                      • 8. Re: Target with an added Percent
                        Allison Pacek

                        I didn't know how to do a mockup in tableau which is why i tried in excel. (i would want to only show FY15 Customer Count and the FY15 Target Customer Count if possible.

                         

                         

                        Attachment included of real results. the idea is to have the Territory try to hit that target and at the end of the year they get a compensation based on whether or not they hit their customer count target (or 10% more unique customers)

                        • 9. Re: Target with an added Percent
                          Matt Lutton

                          No need to use Tableau for the mock up.  When I say "mock up", I just mean a visual representation of your expected output.  The mockup you provided was helpful, but it was not based on the data in this Tableau workbook -- what I am suggesting is to base your mockups on the data present in the workbooks you post (so it is easier to verify results).

                           

                          I'm still a bit unclear on what you're after -- you can modify the formula I've created, as needed, to change the way it is being evaluated.  If you're unable to modify it to get what you need, please provide a mock up of your expected results for this particular dataset   In other words, provide the results you'd expect to see for a few of the Rows shown in the screenshots provided.

                           

                          Cheers!

                          • 10. Re: Target with an added Percent
                            Matt Lutton

                            Let me know if this is it:

                             

                            IF attr([INVOICE_DATE (Years)]) == #1/1/2014#

                            THEN (LOOKUP([Customer Count],1)*1.1) else [Customer Count]

                            END

                            • 11. Re: Target with an added Percent
                              Allison Pacek

                              See my attachment on the mock up. I want to be able to see FY 15 Customer Count, FY15  Target and FY14 Customer Count.

                              Thanks you have me almost to a resolution.

                              • 12. Re: Target with an added Percent
                                Matt Lutton

                                There are several ways you could get to that, based on what I've provided.  I'll let you try and work out the rest - one relatively easy option (that may or may not work for you) would be to use a separate sheet for one of your measures, then combine the sheets on a dashboard.

                                 

                                I'll come back to this later if you still need help working out a layout.

                                • 13. Re: Target with an added Percent
                                  Matt Lutton

                                  Here's a screenshot showing an example of two sheets on a dashboard.  I've manually created the "2015 target" header using a String on the Columns shelf, with YEAR(Invoice Date) on the Detail shelf so it is avialable to the calculation, but not showing up in the view.

                                   

                                  Dashboard 2 sheets.png

                                   

                                  I'm posting this as I don't have much time to help more today.  Getting everything into one sheet is probably very possible, as well, but I'm not able to do it as quickly as I might like.

                                   

                                  Someone else may feel free to adjust what I've done here, in order to create a single sheet with all three measures on it.  We might have to change the way we approach the calculation, in this case, as I was imagining that you wanted to replace the actual values with the Target values.  That is what my calculation does.'

                                   

                                  Cheers.