5 Replies Latest reply: Dec 5, 2016 5:58 AM by Sunny Talwar RSS

    If statement after grouping and aggregating

    Siddharth Sheshadri

      Hello folks,

       

      I have two tables in my data model, both connected with a primary key called "Case ID"

       

       

      In table 1, I have a bunch of Case IDs and their amounts. In table 2, I have a few of the Case IDs from table 1 (not all) with a cap on the amount.

       

      Looking at the below tables, the amount of case ID 1 is 27000, however according to table 2, the case ID 1 should be capped at 17000, how can I achieve this?

       

       

      I tried the below formula but it did not work. The 2 Case IDs are associated in the data model.

       

      IF(AGGR(SUM([Amount]),[Case ID])>SUM([Capped Amount]),SUM([Capped Amount]),SUM([Amount]))/

       

       

      Table 1:

       

      TimeSheet IDAmountCase ID
      10992100001
      10993150001
      1099420001
      1099560002

       

       

      Table 2:

      Case IDCapped Amount
      117000
      36000