1 Reply Latest reply on Nov 6, 2013 7:55 PM by gioel.molinari

    How to calculate difference between measures in two different tables

    gioel.molinari

      I have a scenario that I am trying to replicate where I have two tables on a dashboard and want to show a third table that is the difference between the two table's values. What makes it more tricky is that there is a time dimension: I want to be able to pick Date1 for filter on Table1 and Date2 as a filter on Table2. The Diff table would show me the difference between the values at Date1 and the values at Date2.

       

      I attach my workbook (v8.0 this time). I can't tell what kind of calculated field I need to make since I have to reach across two different tables.

       

      Perhaps there is a better way to do this than as three tables (ie one table with three entries in each cell but I would still need to be able to control Date1 and Date2.

       

      Thanks much!

        • 1. Re: How to calculate difference between measures in two different tables
          gioel.molinari

          Revising and refining this question. I am at the point where I figured out how to calculate the difference between the first and last date in a range using LAST(). One snag I have run into is that my data also has a 'check' dimension that I want to display as a color and what is happening is that LAST() is returning true multiple times because it appears to be partitioning by the check dimension, so it is returning the last date for all the states of the check dimension.

           

          The workbook attached illustrates the case. You can see that some entries in the Diff table have two numbers. If I remove the Check dimension from the color bin the table becomes what I want.

           

          I think the solution lies somewhere in using the MAX function on date, since I only want the last value by date that corresponds to a given concept row to be used in my calc.

           

          thanks much .. i feel like I am close here