1 Reply Latest reply on Mar 28, 2011 8:32 AM by Joe Mako

    How to make To Date comparisons?

    Richard Kalmeijer

      Hi,

       

      I would like to compare data from different periods with eachother and I do not see how to do that yet.

      1. To compare f.e. Sales from this year untill today (28 March 2011) with Sales of Last year until same date last year (28-3-2010).

      2. Same question just for the month March to date 2010 with March to date 2011

      3. Same question with February and March to date for this year.

       

      I imagine that it is possible to make a one off solution, but I am looking for a solution that automatically compares the right dates

        • 1. Re: How to make To Date comparisons?
          Joe Mako

          This is a great example of partitioning custom table calculations. I'll try to explain what I understand.

           

          Take a look at the attached workbook.

           

          Each sheet is displaying three table calculations, the one named "yoy difference of ytd running" is the calculation you are interested in for a difference between the running total for each range. "yoy difference" is not a calculation you would need, only demonstrated here to show the difference between the calculations. "ytd running sum" is both for demonstration, and its result is used in the calculation of the "yoy difference of ytd running". Both "yoy difference" and "ytd running sum" were created from a Quick Table Calculation and were turned into custom table calculations from the "Customize" button from the Edit Table Calculation dialog. They were turned into custom table calculations because Quick Table Calculations cannot be nested.

           

          The reason why you need a nested table calculation is because you need a different partitioning for ytd and yoy.

           

          For the field/pill "ytd running sum", if you open the "Edit Table Calculation" dialog, you will see that the Compute using is set to "Date", Restarting every is set to "Year of Date", and the At the level is left to its default "Deepest". This does what is says, it restarts the running sum for each year.

           

          For the field/pill "yoy difference", if you open the "Edit Table Calculation" dialog, you will see that the Compute using is set to "Date", At the level is set to "Year of Date", and the Restarting every is left to its default "None". This one may not be as clear, by setting the At the level, to "Year of Date", you are telling it to compute the year over year difference.

           

          For the field/pill "yoy difference of ytd running", if you open the "Edit Table Calculation" dialog, you will see that it is set similarly to "yoy difference", but there is an additional drop-down for selecting the Calculated field at the top. This is the option you get when you nest custom table calculations, and allows you to set different partitions for each named table calculation function. If you had combined all functions into a single formula/calculated field, then you would not be able to set different partitioning.

           

          Notice that the pill "YEAR(Date)" must be on the worksheet in order for these table calculation partitions to work.

           

          I added an extra sheet called "Day of Year Table Calculations" to show the values, so you can see how they are adding up to calculate their values. ytd is running down, and the yoy is across. (I had to modify the partitioning slightly to get it to work properly for this crosstab layout).

           

          The other two sheets are these same calculated fields, but with Month instead of Year used in the Edit Table Calculation dialog, and then because Month is selected, the pill "MONTH(Date)" is on the worksheet, and the order of the two dimension pills, "MONTH(Date)" and "YEAR(Date)", is the difference between the sheets (along with a filter to only show the months of interest, the ones used in the calculation).

           

          For each of these view I am showing extra panes, and these can be hidden, I left them shown so you can see the values that are being used in the calculation.