4 Replies Latest reply on Nov 1, 2018 1:08 PM by Daniel Lawrence

    Fixed LOD calculation in the Tooltip

    Daniel Lawrence

      Hello,

       

        I am trying to find YOY data in the tool tip without using the Lookup function.  I have had success using the Lookup function but only when the entirety of the data is in the view.  I want to be able to allow the user to change the date view which would potentially filter out the data needed for YOY calculations, but I want the tooltip to maintain those YOY values/calculations. 

       

      As can be seen in the attached example, I have attempted to fix the previous year date via dateadd without success.  I have also tried variations of this involving datepart(), dateadd(), datetrunc() and the basic [Order Date] - 365 with no success.  Does anyone have an idea of how to make this possible?

       

      Thank you for your time and efforts!

        • 1. Re: Fixed LOD calculation in the Tooltip
          Deepak Rai

          so what is exactly needed in Tooltip?

          • 2. Re: Fixed LOD calculation in the Tooltip
            Daniel Lawrence

            In the tooltip it shows, the date used in the visualization, the previous year's date, the sales for the date used in the visualization and a field entitled "Previous Year's Sales".  "Previous Year's Sales" should show the SUM(Sales) for the date in the previous year.

             

            Example:

             

            If the date used in the visualization is 8/21/17, then the tooltip should show:

             

            Day of Order Date:  August 21, 2017

            Prior Year of Order Date:  August 21, 2016

            Previous Year Sales:  $2828          (Sum(Sales) for August 21, 2016 )

            Sales:  $4798

             

            This is easy to do with Lookup(sum(Sales), -365) but I want to have the same tooltip values if I set the data filter to only year 2017.

            • 3. Re: Fixed LOD calculation in the Tooltip
              Daniel Lawrence

              I redid the workbook in an attempt to further demonstrate what I would like to show.  In Sheet 1, viz has [order date] by day and the tooltip details out the values for the different LODs.  They are all FIXED LODs holding constant, Day, Month, and Year.  Clearly, holding all three the same is the same thing as running [Sales] vs [Order Date].  Holding [Month] or [Month] & [Day] fixed in the LOD can be verified to be giving the correct values with the succeeding sheets.  My goal is to get the final LOD (LOD Prev Year, Same Month, Same Day) to actually show the value for the previous year on the same date: (e.g. for 8/21/18 it shows SUM[Sales] for 8/21/17).

               

              As can be seen it shows SUM[Sales] for 8/21/18.  I cannot seem to get the correct date syntax to work in the FIXED LOD to hold (Year[Order Date] -1) fixed.

               

              These are my LODs:

               

              LOD Same Month    -   {FIXED MONTH([Order Date]): SUM([Sales]) }       -     CORRECT!

               

              LOD Same Month, Same Day  -  {FIXED MONTH([Order Date]), DAY([Order Date]): SUM([Sales]) }       -     CORRECT!

               

              LOD Same Year ,Same Month, Same Day    -    {FIXED YEAR([Order Date]),MONTH([Order Date]), DAY([Order Date]): SUM([Sales]) }       -     CORRECT!

               

              LOD Previous Year ,Same Month, Same Day    -     {FIXED YEAR(DATE(DATEADD('year', -1, [Order Date]))),MONTH([Order Date]), DAY([Order Date]): SUM([Sales]) }       -    INCORRECT!

               

              Has anyone come across a way to make the final LOD work?  Any of the masters:  Pooja Gandhi, Jim Dehner, Jonathan Drummey ?

               

              Thank you for your time and help!

              • 4. Re: Fixed LOD calculation in the Tooltip
                Daniel Lawrence

                If I create a dimension pill for Year(dateadd('year', -1, [Order Date])) then it works if I convert the pill to a discrete dimension.  Can that be done in the LOD formula?

                My guess is that the current iteration is not performing because it is considered either a measure, continuous, or both.