1 of 1 people found this helpful
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.
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.
Date Issue.twbx.zip 34.2 KB
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):
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 -#