How to write set analysis function based on two condition?

[Anonymous]

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

[ChennaiahNallani · Accepted solution]

try this.

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

[jonathandienst]

Like this:

Sum({<

     FieldB = {$(variable)},

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

>} Field1)

[jonathandienst]

And this

MonthStart(AddMonths(Today(),-([LastMonth]-1)))

can be simplified to

MonthStart(Today(), -(LastMonth - 1))

or

MonthStart(Today(), 1 - LastMonth)

And

MonthEnd(AddMonths(Today(),0))

simplifies to

MonthEnd(Today())

So the whole expression becomes

Sum({<

  FieldB = {$(variable)},

  DateField = {">=$(=MonthStart(Today(), 1 - LastMonth)) <=$(=MonthEnd(Today())"}

>} Field1)

[rahulpawarb]

Please refer below:

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

[Anonymous]

Thank  you Chennaiah..  Its working..

Sharath