0 Replies Latest reply on Oct 23, 2018 2:03 AM by Faizi Matin

    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