Help with Set Analysis

[la_souza]

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)

Could you help me please?

Thank you in advance.

[sunny_talwar · Accepted solution]

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.

[Michael_Tarallo]

Hello Laerte,

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

A Beginners' Introduction to Set Analysis (video)

This should help you understand the basics of Set Analysis and therefore help you craft a proper expression.

Let us know how you do.

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

[la_souza]

Thank you for your quick response.

Your answer is correct.

[sunny_talwar]

Awesome