8 Replies Latest reply on Jul 12, 2018 10:50 PM by Sanket Kelkar

    How to create KPI with sparkline

    Sanket Kelkar

      Hi All,

       

      I am trying to create a sheet which shows KPI value (distinct count of customers) with their QoQ trend in a sparkline. Something like below -

      Image result for kpi with sparklines

      Using COUNTD([Customer Name]), I got 793 unique customers which is right when compared with backend excel file. Now when I try to add a sparkline just below it, this number changes to 4036. I think it is trying to sum up all QoQ values. Please find attached my workbook in 10.4 version. I am looking for 793 as my count and QoQ trendline which shows how many unique customers were added every quarter. I have referred this absolutely awesome video - How to Show KPIs & Sparklines in the Same Graph - YouTube

       

      Can someone please help?

       

      Thanks,

      Sanket

        • 1. Re: How to created KPI with sparkline
          Naveen B

          Hi Sanet,

           

          You need to use the floating objects to arrange accordingly

           

          I have attached the work kindly check the same,

           

          Mark this answer as helpful/correct so that it will help others

          • 2. Re: How to created KPI with sparkline
            kumar bharat

            Hi Sanket,

            I suggest check this:

            Tableau Public

            Tableau Public

            BR,

            bharat

            • 3. Re: How to created KPI with sparkline
              Sanket Kelkar

              Hi Naveen,

               

              Thanks for your reply.

               

              If I sum up all values on my sparkline sheet, it gives me 4036 while my actual distinct count is 793. What I am looking for is QoQ count of unique customers which should sum up to 793.

               

              Can you please help?

               

              Thanks,

              Sanket.

              • 4. Re: How to created KPI with sparkline
                Sanket Kelkar

                Thanks, but could not find what I am looking for.

                • 5. Re: How to created KPI with sparkline
                  Naveen B

                  Hi Sanket,

                   

                   

                  When i check the total i am seeing 793 may be i am missing some thing could you please provide some screenshot for the same so that i will look in to more

                  • 6. Re: How to create KPI with sparkline
                    Mahfooj Khan

                    Hi Sanket,

                     

                    Are you looking for this?

                    Mahfooj

                    • 7. Re: How to create KPI with sparkline
                      benjamin pasmore

                      What I think you are doing is summing the quarterly CountD (Customer Name) which is giving you the wrong data for your title.  You can fix this with a LOD calculation.  The trick is to add a LOD expression as a detail to the sheet and then you can include it in the title.  Rather than attach a workbook with the solution, I will give the steps as it is hard to figure out how to repeat this one if you are just looking at a completed workbook.

                       

                      You can also do this with two worksheets and a dashboard if you want, but the below solution will create one worksheet.   Then if you want to do tiles with the various KPI's and sparklines, you can add the various worksheets to a dashboard and you will end up with something similar to your example. 

                       

                      Steps:

                      1) Go to the marks shelf and right mouse click and select "New Calculation"

                      2) Enter in the calculation capsule {FIXED: COUNTD([Customer Name])} and set it as a "Detail" mark

                      3) Goto the title and select edit title

                      4) In the edit title box, go to Insert and select {FIXED: COUNTD([Customer Name])} from the drop down list

                      5) Fix the formatting in the title to be what you need - font color, font sizes, background color

                      6) Fix the Formatting for the graph to remove headers for each axis, remove grid lines for the chart, and change background color.

                      screen shot.jpg

                      • 8. Re: How to create KPI with sparkline
                        Sanket Kelkar

                        Hey all,

                         

                        thank you for the help! really appreciate..!

                         

                        I got it working using { FIXED : COUNTD([Customer Name]) } which gives me 793 and plotted a sparkline using below steps -

                        1. Fixed Month = { FIXED [Customer Name] : MIN([Order Date]) } - Gives me min order date for each customer

                        2. New Cust = IF [Order Date] = [First Month] THEN [Customer Name] END - Matched min date generated in #1 with the date that I have in database.

                        3. Count Cust = COUNTD([New Cust]) - To give me the count.

                         

                        The output - If you can see, my total count is 793 which matches with the sum of all points shown on the dashboard (red box). This shows, Very less *new* customers are getting added after 2017 Q4.

                         

                        Thanks,

                        Sanket