Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sharathkumara
New Contributor III

How to write set analysis function based on two condition?

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

1 Solution

Accepted Solutions
Highlighted
ChennaiahNallani
Contributor III

Re: How to write set analysis function based on two condition?

try this.

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

5 Replies
Highlighted
ChennaiahNallani
Contributor III

Re: How to write set analysis function based on two condition?

try this.

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

MVP
MVP

Re: How to write set analysis function based on two condition?

Like this:

Sum({<

     FieldB = {$(variable)},

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

>} Field1)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: How to write set analysis function based on two condition?

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rahulpawarb
Valued Contributor III

Re: How to write set analysis function based on two condition?

Please refer below:

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

sharathkumara
New Contributor III

Re: How to write set analysis function based on two condition?

Thank  you Chennaiah..  Its working..

Sharath