3 Replies Latest reply on Mar 20, 2017 5:12 AM by Arran McInally

    Level of Detail over a date range?

    Arran McInally

      Hi!

       

      Is it possible to do a level of detail expression across a date range?

       

      At the moment I can return, say, a yearly average like:

      { FIXED [ProductID], YEAR([Days]) : AVG(Sales)}

       

      But for data from now (February) the average will only calculate back to the beginning of January. Is there an expression like:

       

      { FIXED [ProductID], datediff('years', -1,[Days]): AVG(Sales)}

       

      I cannot use a table calculation for this purpose!

        • 1. Re: Level of Detail over a date range?
          Jonathan Drummey

          What you're looking for is kind of a LOOKUP() functionality in LOD expressions and they don't do that presently. You can start faking some of it by nesting multiple LOD expressions but that can quickly run into performance issues.

           

          I'm curious, why are table calcs not an option?

           

          Jonathan

          • 2. Re: Level of Detail over a date range?
            Arran McInally

            How expensive a hit to performance? The data barely needs to be touched past this point so the performance hit would be worth the result I think.. Can you explain how LOOKUP() can be used in LOD or an example?

             

            The reason for no table calcs is because the result post calculation needs to be aggregated again. In a case I am working on I need to count monthly outliers that fall outside a rolling average/SD range. Using a table calculation can give me a solid visual representation of this for sure, but at a rolling daily level, I need to digest that visual result into a raw data form, ideally a table by month showing the count of outliers.

             

            I can do the above with a static average (using LOD like in the parent question) but rolling values would be ideal for the data I want to represent.

             

            I actually have question live for the whole problem rather than this bitesize chunk:

            Aggregating a table calculation - or LOD alternative?

            • 3. Re: Level of Detail over a date range?
              Arran McInally

              If anyone is curious or wants to do this for the future I managed it like so, where Rolling Date is a bool to enable or disable rolling values and Report Data is a parameter that is locked to monthly values. The below returns the last three months (excluding the current month) of daily values. Can do the median/SD of these results as needed.

               

              { FIXED [Date],[Product ID]:

              IF [Rolling Date?] THEN (

                  IF

                  MIN([Date]) >= DATEADD('month',-3, [Report Date])

                  AND

                  MIN([Date] <= dateadd('day',-1,dateadd('month',1,datetrunc('month',[Report Date])))) THEN

                       min([Daily Value])

                  END

              )

              ELSE

              min([Daily Value])

              END

              }