Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

la_souza
New Contributor

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)

 

Could you help me please?

 

Thank you in advance.

 

1 Solution

Accepted Solutions
MVP
MVP

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.


Capture.PNG

4 Replies
MVP
MVP

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.


Capture.PNG

Employee
Employee

Re: Help with Set Analysis

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
New Contributor

Re: Help with Set Analysis

Thank you for your quick response.

Your answer is correct.

MVP
MVP

Re: Help with Set Analysis

Awesome