2 Replies Latest reply on Nov 26, 2016 3:50 AM by Bora Beran

    Min and Absolute Minimum Calculations with Continuous Dates

    Justin Flynn

      I'm trying to illustrate to my son how inflation works, so I downloaded Consumer Price Index data to create a visualization of item prices over time.  Items are not always measured though.  Some items are taken out, and others added.  To reduce the number of items we're looking at, I thought I would only include items that have been measured across time for the entire dataset.  I have a calculation that will tell me this, but as soon as I apply it to a sheet with a continuous measure, the calculation returns true for everything. 


      How can I create a calculation that returns true or false for items that have and have not been included across the whole dataset?  The sample worksheet attached has two sheets.  The first has the item price trend over time.  The second has the calculation to determine if an item was measured from the beginning of the dataset.

        • 1. Re: Min and Absolute Minimum Calculations with Continuous Dates
          Norbert Maijoor

          Goodmorning Justin,


          In principle you would like to create a table with all individual dates which can be used as "backbone" and the actual data can be attached to.

          The methods is called "scaffolding" and explained here

          • 2. Re: Min and Absolute Minimum Calculations with Continuous Dates
            Bora Beran

            If I am understanding the question correctly you're trying to see if a selected date range has any missing values or not.


            Data seems to have 1 row for each month and an item appears in multiple area codes.


            So it looks like you need to compute what the full range is first. Let's call it [Range in months].


            DATEDIFF('month',{MIN([Measurement Date])},{MAX([Measurement Date])}) + 1


            If you have 2 rows of data e.g. Jan, 1980 and Feb, 1980. The calculation above will give you 2.


            Then you can write something like this which first sees if a given item, area combination has as many rows as expected as defined by range in months. If it doesn't it marks the area, item combination as missing values.


            This would result a given item possibly to have some area codes where it is missing stuff and some where it has the full range hence a mix of complete and missing.


            Second level of aggregation picks the max value of this set of complete and missing. Max takes the alphabetical max so if an item has any missing in any area code, it will return missing.


            IIF({fixed [Item Name] : MAX(IIF({fixed [Item Name], [Area Code] : SUM([Number of Records])}==[Range in Months], 'Complete','Missing Values'))}=='Missing Values','Missing Values','Complete')


            I hope this helps.