4 Replies Latest reply: Feb 19, 2016 12:33 PM by Sunny Talwar

Help with Set Analysis

I’m new with Qlik Sense and I’m having some problem to do a Set Analysis.

I have a database with this fields:  Client, YearId, MonthId, Asset and Balance

If I select in a variable one year (ex: 2016) and one month(ex:1) I need to do this things:

Find all Clients that have sum(Balance)<>0 in YearId=2015 and MonthId=1 that doesn’t have sum(Balance) in YearId=2016 and MonthId=1. This means, the Clients that had Balance in past and don’t have Balance today.

After this for this Clients, I need to know the greater Balance in the last 2 Years. In this example, the greater Balance since 2014.

I try many things but I can’t find the correct answer.

Attached a file with the data example.

The correct answer to this data is: Client=2 and Balance=720 (Greater Balance was in 2014/01)

• Re: Help with Set Analysis

Can you try this:

Max(Aggr(Sum({1<Client=p({1<Client={"=Sum({<YearId={\$(vYear_1)}, MonthId={\$(vMonth)}>}Balance)<>0"}>}) *

e({1<Client={"=Sum({<YearId={\$(vYear)}, MonthId={\$(vMonth)}>}Balance) <> 0"}>}), YearId>} Balance), Client, YearId))

I am getting values as 2015 and 690 because 2014's sum is 660.

• Re: Help with Set Analysis

Thank you for your quick response.

• Re: Help with Set Analysis

Awesome

• Re: Help with Set Analysis

Hello Laerte,

In addition, take a look at these two videos - starting with this one:

Introduction to Set Analysis (video) - Part 1

Let us know how you do.