New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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;

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

but did't worked

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

Regards,

Sharath

1 Solution

Accepted Solutions
Highlighted
Contributor III

try this.

5 Replies
Highlighted
Contributor III

try this.

MVP

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

Like this:

Sum({<

FieldB = {\$(variable)},

>} Field1)

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

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

And this

can be simplified to

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

or

MonthStart(Today(), 1 - LastMonth)

And

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
Valued Contributor III