Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
la_souza
Contributor
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
sunny_talwar

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

View solution in original post

4 Replies
sunny_talwar

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

Michael_Tarallo
Employee
Employee

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

Regards,
Mike Tarallo
Qlik
la_souza
Contributor
Contributor
Author

Thank you for your quick response.

Your answer is correct.

sunny_talwar

Awesome