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



      Table 2:

      Case IDCapped Amount