4 Replies Latest reply on Jul 5, 2017 4:38 PM by Okechukwu Ossai

    get the count of living person on particular date

    sanjeev pandey

      table has three column personID, birtDate, deathDate


      Requirement:X axis range from Min of Birth date to max of death date

      Y axis person living on particular date (for a value from x axis those person will be counted whose birth is before the value in x axis and death after the value in x axis)


      Please help....Thanks in advance

        • 1. Re: get the count of living person on particular date
          Okechukwu Ossai

          Hi Sanjeev,


          How do you want to use the result? Table, chart or interactive display with parameter?

          Could you give an example of what the result will look like? You can pick some dates and say what the expected result should be. This will ensure the solution provided is exactly what you are looking for.



          • 2. Re: get the count of living person on particular date
            Amit Narkar

            Hi Sanjeev

            You can start with Data Pivot



            Then you need to simply use count of events on each date. Please find below snapshot.

            Here count event gives number of births or deaths in particular year.


            You can filter the data to only focus on births as per your requirements.


            • 3. Re: get the count of living person on particular date
              sanjeev pandey

              Thank you  Okechukwu, I hope this will give a idea, start and end date will be provided by user, and we need to have count of living person wrt x axis.


              • 4. Re: get the count of living person on particular date
                Okechukwu Ossai

                Hi Sanjeev,


                It will be challenging to achieve the desired requirement using your data as it is currently structured.

                From what I understand, the X-axis should be a continuous date controlled by parameters. However, there are gaps in your Birth Date and Death Date fields. Therefore, I created a Calendar data source which contains continuous dates from 01/01/1967 to 31/12/2010. Next, I pivoted your original data, combining Birth Date and Death Date into one field called Date and the labels as Status. I then Left joined the pivoted original data to the Calendar data source.



                Having reshaped the data, I followed the steps below;


                Step 1: Create calculated field [Death Date]

                {FIXED [Person], [Date]: MAX(IF {FIXED [Person]: COUNTD([Status])} = 2 THEN {FIXED [Person]: MAX([Date])} END)}

                This calculates (fixes) death date per person per row. This is required for row level date comparison in latter steps.


                Step 2: Create calculated field [New Persons]

                {FIXED [Date], [Person]: SUM(IF [Date] < [Death Date] THEN 1 ELSE 0 END)}

                This calculates new additions to the population. A value of 1 is assigned to any person who is still living on any particular day; that is date < death date.


                Step 3: Create calculated field [Living Persons]

                RUNNING_SUM(SUM([New Persons]))

                This creates a cumulative sum of new persons. This is equivalent to the total count of living persons on any particular date. Since [Living Persons] is a table calculation, a direction of calculation needs to be specified. Choose "Compute using" Table (across) or Table (down) where appropriate.


                Step 4: Create calculated field [Valid Date Axis]

                {FIXED [Date], [Person]: MIN(IF [Date] >= { FIXED : MIN(IF [Status] = "Birth Date" THEN [Date] END)} AND

                [Date] <= { FIXED : MAX(IF [Status] = "Death Date" THEN [Date] END)} THEN [Date] END)}

                One of your requirements is that X axis should range from Min of Birth date to max of Death date. This formula achieves that requirement. However, you want these Valid Dates to be controlled by user parameter selection.


                Step 5. Create parameters [Start Date] and [End Date]

                These will be used to limit the displayed valid dates on the X-axis based on user selection.


                Step 6: Create calculated field [Display Date Filter]

                LOOKUP(MIN([Valid Date Axis]),0) >= [Start Date] AND LOOKUP(MIN([Valid Date Axis]),0) <= [End Date]

                This is a boolean expression which is used to link Start and End Date parameters back to the data. Put this field on the filter shelf and set to True. For the chart, choose "Compute using" Table (across).

                Lookup() function was used so the filter is applied to the view only without filtering the underlying data.


                See attached workbook in version 10.0 and the reshaped Excel data source.


                Hope this helps.




                1 of 1 people found this helpful