5 Replies Latest reply: Feb 1, 2018 10:30 AM by Sharath Kumar A RSS

    How to write set analysis function based on two condition?

    Sharath Kumar A

      Hi All,

      I want to find sum of particular field based on two or more condition, let me elaborate this;

      1. I want to find  sum of "Field1"

      2. one condition I want to check is if variable  value is matching with some field then find sum, code is below;

      sum(if(FieldB=$(variable),Field1))

      this is working fine.

      3. along with this I want to restrict  data for sum of "Field1" is for last N (Say 3,6,9,12) months. for that I am using below code;

      sum({<DateField = {">=$(=MonthStart(AddMonths(Today(),-([LastMonth]-1))))<=$(=MonthEnd(AddMonths(Today(),0)))"}>}Field1)

      where LastmMonth value will be 3,6,9 or 12

      this is also working fine.

      4. Now I want to add both 2 and 3 condition to find sum of FieldA, I used following code

      sum(if(FieldB=$(variable) and {<DateField = {">=$(=MonthStart(AddMonths(Today(),-([LastMonth]-1))))<=$(=MonthEnd(AddMonths(Today(),0)))"}>},Field1))

      but did't worked

      So I request you can  you suggest how to check two condition in set analysis to use aggregate functions.

       

       

      Thanks in advance..

      Regards,

      Sharath