5 Replies Latest reply on Aug 28, 2013 1:25 PM by AJ Fahmy

    How to create a KPI that will only display the Last date it has values?

    AJ Fahmy

      I need to create a KPI that calculate the # of Twitter subscribers wither up or neutral or down based on date. See attached WB. I want to create a KPI that will look at the data set and look up the last date that has a value and compare it to the previous day and see if the # of subscribers went up or down or non.

      So what I did is I created a calculated field “total Followers” (IF datetrunc('day', [Date]) = datetrunc('day', [Select Date] )  THEN [followers_count] ELSE 0 END)

      Also, I created “Previous Day Followers” (IF DATEDIFF('day', datetrunc('day',[Select Date]), datetrunc('day',[Date] )) = -1 THEN [followers_count] ELSE 0 END)

      Another calculated field to calculate the Differences from Previous Day Followers witch is ([Total Followers]-[Previous Day Followers])

      My KPI is (IF SUM([Difference From Previous Day Followers]) < 0 THEN "Down"

      ELSEIF SUM([Difference From Previous Day Followers]) = 0 THEN "Neutral"

      ELSE "Up"

      END)

      [Select date] is a straight forward date parameter

      Based on the Excel sheet, last date is 08/24. The problem is when you select a date outside the date range “08/25” the KPI will display  (-3,691). WHY? And How to fix that where the KPI will only display the Last date it has values

       

      1. Thanks.
        • 1. Re: How to create a KPI that will only display the Last date it has values?
          Sampath Rao Dheekonda

          Hi AJ,

           

          I hope you are having issue with identifying the Maximum date in the available data.

          Use the below formula to get the latest or maximum date

           

          "Latest Date"  WINDOW_MAX(MAX([Date]),FIRST(),LAST())

           

          And to avoid issues when selecting the date outside of date range you can use the below formula to get the latest date from the data. the following variable checks if the selected date is greater than the available maximum date it will consider that date else it will consider the selected date.

           

          "Max Date"   IF[Select Date]>[Latest Date] THEN [Latest Date] ELSE [Select Date] END

           

          Use Max date in your calculations to avoid your data issue. I hope this helps.

          Let me know if you have any further clarifications.

           

          Regards,

          Sampath

          1 of 1 people found this helpful
          • 2. Re: How to create a KPI that will only display the Last date it has values?
            Sampath Rao Dheekonda

            Hi AJ,

             

            I tried with above mentioned variables but unable to do the calculations using the dates mentioned above.

            When I'm trying to use those dates in the calculations I'm getting the error.

             

            Time being you can this work around. Create a separate column in the spread sheet and it should give you latest date in the complete column and load that data into tableau.

             

            Now can create condition object to check about the latest date i.e. date available or selected date

             

            Use this forumla to check latest from the two dates :

            "Comparision Date" IF([Select Date]>[Latest Date]) THEN [Latest Date] ELSE [Select Date] END

             

            And update your forumlas for Total followers and Previous followers like below

             

            "Total Followers"  (IF ([Date] = [Comparision Date])  THEN [followers_count] ELSE 0 END)

             

            "Previous Followers" (IF DATEDIFF('day', [Comparision Date], [Date] ) = -1 THEN [followers_count] ELSE 0 END)

             

            I hope this will solve your issue for timebeing

            .

            Find the attached twbx file for your reference.

            • 3. Re: How to create a KPI that will only display the Last date it has values?
              Dana Withers

              Hi AJ,

               

              Sidetrack perhaps, but with reason... why do you want to select a date using a parameter?

              I think your biggest problem is that you're allowing users to pick something that will break everything (and in addition, will make absolutely no sense).

               

              I may not have understood correctly - if so, please attach a workbook or image of what you're looking for. However...

               

              I think you want to see the brands, last days and an indication based on difference in followers. You can do this with a table calc on followers_count (one that does difference compared to previous based on the pane/brand). You can then show only the last row in each brand and give the user a simple date slider to select which maximum date he wants to see. That slider will never give you an option to pick a date that is not there, so no room for users to break your intentions.

              See result for two dates (very badly coloured by whether it is up, down or neutral):

              KPI1.jpg

              KPI2.jpg

              • 4. Re: How to create a KPI that will only display the Last date it has values?
                AJ Fahmy

                Thank you Sampath, Dana for your replies.The KPI that i'm creating is total Twitter followers UptoDate. I was able to do the metrics however my dilemma was when the client select date outside the range of dates in the database. Dana, the client asked for a parameters to allow him to select the date he inquires. The problem is if you select a date outside the date range, the KPI will point down and the total #s of the followers in a negative number because my calc field is Today followers - yesterday followers, so if a client select a date outside the DB date range which will have a 0 and negative that from the sum of the current followers that's when you get the big negative drop. So I was looking for a solution to view the last date has data and not display this -#. The solution to this issue is I had to add a CTE field in the database and called it MaxDataDate ((select MAX(Date) from ********) AS [MaxDataDate],) and create a calculation field IF[Select Date]> MaxDataDate THEN MaxDataDate ELSE [Select Date]  where it will prevent the KPI from displaying any numbers not in the database. Even my Date parameter selection is 08/29 which is outside the database range where last field has data is on the 27th

                Now below is the old issue from last week where when I selected a date outside the database range I get this big drop big -#