2 Replies Latest reply on Jul 8, 2016 8:44 AM by Joe Oppelt Branched from an earlier discussion.

    Get Data only for Last Day, where last day isn't today

    Leonard Culberson

      Hello Simon!

      I would like to ask you a question.  I have been trying to find an answer on tableau community, but no luck.

      I am trying to count, sum, number of records on a particular date, MAX(DateReceived) date.  I would like to know how many records were received on this date.  This particular date happens to be 2 days ago, 07/06/16.

      I have tried many different ways, but can not seem to find a solution.

      I have also tried to POST this message to the community, but can't figure out how to do that.  I am new to using the community forums.

      Any information you can share would be great, or if you can tell me how to ASK the community, that would be great too.

      I know you are probably very busy.

      Thanks for any help!

      Leonard Culberson

      Leonard.Culberson@Wellstar.org

        • 1. Re: Get Data only for Last Day, where last day isn't today
          Simon Runc

          hi Leonard,

           

          ...and welcome to the community (it really is one, if not the, best parts about Tableau)!!

           

          So I've branched your question into a new thread...to make it easier for others, with a similar problem, to find the solution (assuming I can find one!!)

           

          So to ask a question goto Forums

           

          and then click on 'Start a discussion'

           

           

          and ensure you tick the 'Mark this as a question'

           

           

          I'd also point you to the 'New Starters' guides in suggested reading (on the right hand pane in the main forum)

           

          So onto your question....

           

          So the easiest way to do this is to create a FIXED LoD which brings back the last date in your data (so doesn't matter if its yesterday, 2 days ago, 2 years ago....)

           

          [Last Date of Data]

          {FIXED MAX([DateReceived])}

           

          As a fixed LoD, with no dimensions in the FIXED part, this calculates the MAX date of the data, and then returns this to every row in the data....If you've not come across the various calculation types and what they do/how they work, you might find this useful Answer - Quora

           

          Once we have this, we can easily create calculations which only contain the data form that day

           

          for Example

          [Number of Records on Last Day of Data]

          IIF([DateReceived] = [Last Date of Data], 1,0)

           

          and then once this is brought into the canvas as a SUM, it will be the number of records for the last day.

           

          Hope that makes sense, but please post back if not.

          1 of 1 people found this helpful
          • 2. Re: Get Data only for Last Day, where last day isn't today
            Joe Oppelt

            If you're using 9.0 or higher, create this calc:

             

            Max Date Received:

            {fixed : max([DateReceived]) }

             

            This will give you the last [Date Received] in your data set.

             

            If you always want "Yesterday", you can create this calc instead:

             

            Yesterday:

            TODAY()-1

             

            If you always want two days ago, create this calc:

             

            Two Days Ago

            TODAY()-2

             

            Once you have the date you want to compare to, you can just use that calc in other calcs like this:

             

            SUM ( IF [DateReceived] = [Max Date Received] then [Sales] end )

            2 of 2 people found this helpful