5 Replies Latest reply on Dec 12, 2016 10:12 AM by Andrew Watson

    Display prior year value in current year date in a line graph

    J.R. Wilton

      Hello,

       

           I am trying to display last year value in a current date graph.  To give you an example, my data (cannot be altered)  is in this format

       

      Date     Value

      1/15     10

      2/15     10

      1/16     20

      2/16     20

       

      I currently have a graph that has Date on it axis.  I have a parameter selector that says absolute and prior.  The formulas are as followed

       

      TY

      If date <= paramdate and date >= dateadd('week',-8, paramdate) then value else 0 end

       

      LY

      If date <= dateadd('year',-1,paramdate) and date >= dateadd('year',-1,dateadd('week',-8, paramdate)) then value else 0 end

       

      Now if I just put them by themselves they put up the correct number, however when I add a filter on a line graph to limit it to paramdate to paramdate - 8 weeks, the LY formulas doesnt work anymore because there is no last year value for the filter  paramdate to paramdate - 8 weeks.  How do I put last year number as this year date.

       

      Thanks

        • 1. Re: Display prior year value in current year date in a line graph
          Andrew Watson

          If you want to move this year to last year you could create a new date field, to use as your axis and probably in your calculated field:

           

          IF YEAR([OriginalDate]) = YEAR(TODAY()) THEN [OriginalDate] ELSE DATEADD('year',1,[OriginalDate]) END

           

          That formula will align your dates - however I think you will also need another field to identify whether it's this year. You could just use the YEAR([OriginalDate]) to do this, in which case there's no need to create another field.

          • 2. Re: Display prior year value in current year date in a line graph
            J.R. Wilton

            This has started me on the right path.  However I am stuck on 1 last piece now.

             

            Current Smart Date Calculation

            CASE [Value Select]

            WHEN 1 THEN [DATE]                                         --This Year

            WHEN 2 THEN DATEADD('day',364,[DATE])       --Last Year

            WHEN 3 THEN DATEADD('day',364,[DATE])       --This Year - Last Year

            WHEN 4 THEN DATEADD('day',364,[DATE])       -- (This Year - Last Year)/Last Year

            ELSE [True Booked Date] END

             

            The primary problem now is the This Year - Last Year calculation.  Because I now overlay it on the same graph, it does only one and not both.  If I try to throw a CASE statement in there then it will error out for boolean.  Any idea.

            • 3. Re: Display prior year value in current year date in a line graph
              Andrew Watson

              It's not clear what you're trying to do - the above formula will return the same result for 2, 3 and 4.

               

              However you've asked about 'this year' - 'last year'. To do that you would need to isolate the value for this year and the value for last. For example, assuming your measure is called 'Orders', for this year you could have a formula:

               

              IF YEAR([Date]) = YEAR(TODAY()) THEN [Orders] END

               

              and last year could be:

               

              IF YEAR([Date]) = YEAR(TODAY()) - 1 THEN [Orders] END

               

              Then to find this year minus last year you would use those formulae, i.e. SUM([ThisYear]) - SUM([LastYear])

               

              Hope that points you in the right direction.

              • 4. Re: Display prior year value in current year date in a line graph
                J.R. Wilton

                Andrew,

                 

                     Thank you for all of your help, I was able to complete the tasks however the senior leadership change a fundamental requirement on me.  They say that the start and end date of the date range must be in a slider format.  This mean the only way to do a slider is to make it a filter.  Since I am making it a filter, my last year value are coming back 0 because of the filter results.  Is there any way to get last year value even with a filtered item?

                 

                Thank you,

                • 5. Re: Display prior year value in current year date in a line graph
                  Andrew Watson

                  If you're using a filter this will remove last year from the data, as you've found. It is possible to make a 'late' filter using a table calculation, something like LOOKUP(ATTR([Date],0)). However I don't think the table calculation will operate as a slider filter - plus it only affects one worksheet so is less useful on a dashboard with multiple worksheets.

                   

                  Best solution is to explain to your senior leadership the slider is clunky and won't give them what they want, then revert to how you had it