1 Reply Latest reply on Sep 24, 2016 6:56 AM by David Li

    Using LOD and Table Calculation at the Same Time

    Vivian Cheng

      I have the % change (Month-over-month) for billed revenue for each customer for the past five years (which is a table calculation).

      If I want to assign a label to the customer based on this value, for example, if at one point in time the % change exceeds 500%, then label this customer as "fraud". (which is assigning a dimension to the customer level).

      I am thinking of using the expression {fixed [Customer ID] : [% change in billed revenue] }, but I am unable to do it since [% change in billed revenue] is a table calculation.

      How can I solve this problem? Thank you!

        • 1. Re: Using LOD and Table Calculation at the Same Time
          David Li

          Hi Vivian! As you saw, LOD calculations can only summarize row-level fields. They don't work on fields that have already been aggregated (including table calculations). However, you can layer table calculations on top of each other, though this becomes quite complicated quite quickly.

           

          Here, you'll only need two layers. The first layer is the one you have already, [% Change]. You can use another table calculation to compare that result to your threshold. Maybe something like:

          IIF([% Change] > 5, "Fraud", "Not Fraud")

          Although this doesn't have any table calculation functions in it, it's still a table calc because it refers to a table calc. In this case, you're lucky that both layers of table calcs will compute along the same dimension--either Table Across (I imagine) or along Customer ID. In other situations, though, you may have to edit each layer separately (using the drop-down in the table calc editor) to assign different compute dimensions.