1 Reply Latest reply on Nov 29, 2018 12:36 AM by Donna Coles

    How to get sum of a measure based on maximum date within a selected time range ?

    Faizi Matin

      I have a below dataset which is restricted with a filter provided between dates 12/01/2018 to 14/01/2018

       

      Name of StoreVisit dateNo of DevicesLatest visit dateCount of devices during latest visit
      Store -0112/01/2018214/01/20181+5=6
      Store -0113/01/2018314/01/20181+5=6
      Store -0114/01/2018114/01/20181+5=6
      Store -0212/01/2018314/01/20181+5=6
      Store -0213/01/2018214/01/20181+5=6
      Store -0214/01/2018514/01/20181+5=6

       

      I need to derive logic for column-3 and column-4 in the above dataset.

      Logic: For a particular store if the latest visit is 14th Jan within a selected filter range then it should count all the no.of devices for that latest visit date.

      For example, If now the range is changed to 12/01/2018 to 13/01/2018 then the result should be like below :

       

      Name of StoreVisit dateNo of DevicesLatest visit dateCount of devices during latest visit
      Store -0112/01/2018213/01/20183+2=5
      Store -0113/01/2018313/01/20183+2=5
      Store -0212/01/2018313/01/20183+2=5
      Store -0213/01/2018213/01/20183+2=5

       

      Note: As we have more filters in the view so fixed LOD and context filter cannot be used. Is there any other approach available for achieving the same.

       

      Thanks in advance.

       

      Regards,

      Faizi

        • 1. Re: How to get sum of a measure based on maximum date within a selected time range ?
          Donna Coles

          Hi Faizi

           

          I'm going to describe as best I can, but you haven't provided a packaged workbook, so I'm making assumptions....  I think you'll want to do something along the lines of :

           

          1) Create a field that stores the Latest Visit Date : WINDOW_MAX(MAX([Visit_Date]))

           

          2) create a field that stores the no of devices if Visit Date = Max Date from filter, something like : Count Devices on Latest Visit : IF ATTR([Visit Date]) = [Latest Visit Date] THEN SUM([No of Devices]) END. THis will provide a field like your 'No of Devices' column but ony have values populated for the rows where the date matches.

           

          3) Create another field that sums this column across all the rows in the table: Total Devices on Latest Visit : WINDOW_SUM([Count Devices on Latest Visit]).

           

          These use table calculations, so you may need to alter the properties of the calculation to ensure the values are being calculated over the appropriate partitions (dimensions).  If these aren't familiar to you, please refer to Transform Values with Table Calculations - Tableau .

           

          Attached is a similar concept using Superstore Data, in v10.1, and uses Order Date and Qty instead of your Visit Date & Device Count.

           

          Hope that helps, please feedback.

           

          Thanks

          Donna