3 Replies Latest reply on Feb 24, 2016 6:49 AM by mohammadirfank.tareen

    Point in time and aggregated data

    mohammadirfank.tareen

      Hi,

       

         I am trying to mix data which can be aggregated and "point in time data" (data for selected end date). I don't know how to achieve this in tableau. When I select my filters the point in time data is getting aggregated which is not correct.

       

      Ex: If I have a column A which aggregates (calculated field). Column B shouldn't aggregate and should be based on end date selected on filter.

       

      Is this something possible in tableau?  

       

      MIT

        • 1. Re: Point in time and aggregated data
          Bill Lyons

          If I understand your question correctly, I believe this can be done in a variety of ways in Tableau. However, the question is quite vague. If you can attach a packaged workbook (.twbx), we can probably help. See Posting a Perfect Question |Tableau Support Community, Attaching a Packaged Workbook and Anonymize your Tableau Package Data for Sharing |Tableau Support Community for more information.

          • 2. Re: Point in time and aggregated data
            Bora Beran

            It is hard to tell without seeing your data but you can easily find the latest value in the series and pick that value.

             

            E.g. let's say your end date is 1/1/2015

             

            You want to get the latest value that is before 1/1/2015

             

            If end date is a filter it will remove all the rows that are beyond 1/1/2015 so first you need to find the max date for level of detail of your sheet

             

            Assume you want to get the max date for each Store

             

            {fixed Store : Max([Date field])} would give you the latest date for each store

             

            If you're trying to find Sales value on this date, you can write something like

             

            IF [Date Field]={fixed Store : Max([Date field])} then [Sales] ELSE 0 END

             

            This gets the Sales value on that date and returns zero for everything else

             

            Your final equation would be

             

            MAX(IF [Date Field]={fixed Store : Max([Date field])} then [Sales] ELSE 0 END)

             

            Since all the other values are 0 this would give you the Sales value on the latest date for that store.

             

            I hope this helps.

            1 of 1 people found this helpful
            • 3. Re: Point in time and aggregated data
              mohammadirfank.tareen

              Thank you for explaining the logic.  My report was based on date filters selected and I was able to achieve this by writing the following calculation.

               

              IF [WEEK_END_DT] = [Week End Date] THEN ZN([NPS_QUARTER_FIELD]) ELSE 0 END

               

              [Week End Date] is my lower date range parameter. Since I don't have hierarchy levels I skipped using LOD expression - FIXED.

               

              Your logic is much more flexible and can be used at different levels of data.

               

              Thank you!

              MIT