1 Reply Latest reply on Mar 16, 2016 5:43 AM by Stephane Marx

    Pulling out Previous Value if current week value is missing

    Sunil Rai

      Hi guys,

       

      (I apologise for asking so many questions without sample, I try to upload samples where I can) This question is simple (for the pros) though so I hope there is no need for samples.

       

      So I created a calculated field called End of Week Inventory by using the formulae - If Week='20160212' then

      Inventory END

       

      However, I would like it to be able to pull out the previous week data for this calculation if that weeks data is missing. My dataset is weekly and the date is a string like this '20160212'. So I want to pull a certain week inventory, but if that is missing, then we take back the previous week, if not the previous week.

       

      My time dimension is "WeekEndDate" and its a string. (weekly).

       

      I suppose I have to dateparse it first before doing any calculation?

       

      Cheers,

      Sunil

        • 1. Re: Pulling out Previous Value if current week value is missing
          Stephane Marx

          Hi,

           

          You should start by trying to change the data type of your field "WeekEndDate" into a date rather than a string.

          In the data pane in the left of the workbook (where you have all your dimensions, the "blue pills") right click the dimension "WeekEndDate"  > Change Data Type > Click Date. You can see now a little blue calendar on the left of the dimension.

          By doing that you're telling Tableau to consider the dimension as a date, which will allow us to manipulate it much better.

          If your data is updated regularly (up to today a a few days ago). We can also use the Date dimension as a much more precise filter. By dragging it onto the filter shelf > choose relative date > last week > click ok

          Right click on the date filter you've just inserted > select show filter

          The filter appears on the top right below "show me" by clicking on it you can change the week you're displaying in the workbook.

           

          If it does not work correctly you can indeed use the date parse function.

          This tableau article explains it well: Understanding the DATEPARSE Function | Tableau Software

          Then using the function datediff can be useful.

          Difference in Dates with one Date Field | Tableau Software