Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

vidamakiling
New Contributor II

Expression within a set analysis

Hi, thank you for taking the time to read my question.

I have this data set below:

EmployeeIDHour1Hour2Salary
1-50500
150250
201700
300500
420250
430700
550500
5-2-1250
5-31500
620250
6-20700

 

And what I'd like to happen is to display only that has a total of 0 for Hour1 and Hour2 and display the sum of the salary as well. Below is the desired result:

EmployeeIDHour1Hour2Salary
100750
300500
500750
600950

 

Here's what I tried to use:

Sum({<Hour1 = {"=sum(Hour1)=0"},Hour2={"=sum(Hour2)=0"}  >}Salary)

But obviously, it is not working. Appreciate your help!

Thanks in advance! 

 

 

 

 

Labels (3)
3 Solutions

Accepted Solutions
michal__
New Contributor II

Re: Expression within a set analysis

Hi,

try this:

Sum(
  {<
      EmployeeID={"=sum(Hour1 + Hour2) = 0"}
  >}
Salary)

pradosh_thakur
Honored Contributor II

Re: Expression within a set analysis

please try below

Sum({<EmployeeID = {"=sum(Hour1)=0 "}  >*<EmployeeID = {"=sum(Hour2)=0 "}  >}


Salary)
Learning never stops.
Highlighted
Partner
Partner

Re: Expression within a set analysis

It is a good practice to avoid Set Analysis as much as possible for two reasons:

  1. It is heavy to process
  2. Sometimes can be complex to create, maintain and even understand

For those reasons we should always try to prepare our data to simplify as much as possible our chart expressions.
For instance, you can modify your Load Script in this way:

[SalaryTable]:
Load * Inline [
EmployeeID,Hour1,Hour2,Salary
1,-5,0,500
1,5,0,250
2,0,1,700
3,0,0,500
4,2,0,250
4,3,0,700
5,5,0,500
5,-2,-1,250
5,-3,1,500
6,2,0,250
6,-2,0,700
];


[CalculatedSalary]:
Load
    EmployeeID,
    TotalSalary
Where
    (TotalHour1 + TotalHour2) = 0
;
Load
   EmployeeID,
   Sum(Hour1) AS TotalHour1,
   Sum(Hour2) As TotalHour2,
   Sum(Salary) AS TotalSalary
Resident
	[SalaryTable]
Group By
	EmployeeID
;

That does the magic and your chart expression can be a simple Sum(TotalSalary)  or even just the field TotalSalary if you don't need to show a chart total.

Salary.GIF

Note: I'm just not sure about EmployeeID 5 salary.

 

Regards,

Mark Costa

 

22 Replies
michal__
New Contributor II

Re: Expression within a set analysis

Hi,

try this:

Sum(
  {<
      EmployeeID={"=sum(Hour1 + Hour2) = 0"}
  >}
Salary)

pradosh_thakur
Honored Contributor II

Re: Expression within a set analysis

please try below

Sum({<EmployeeID = {"=sum(Hour1)=0 "}  >*<EmployeeID = {"=sum(Hour2)=0 "}  >}


Salary)
Learning never stops.
vidamakiling
New Contributor II

Re: Expression within a set analysis

thank you so much! this worked!

Partner
Partner

Re: Expression within a set analysis

HI,

I'm expecting the EmployeeID = 5 to have a salary of 1,250 (500 + 250 + 500). 
Why it is only 750?

 

Regards,

Mark Costa

Highlighted
Partner
Partner

Re: Expression within a set analysis

It is a good practice to avoid Set Analysis as much as possible for two reasons:

  1. It is heavy to process
  2. Sometimes can be complex to create, maintain and even understand

For those reasons we should always try to prepare our data to simplify as much as possible our chart expressions.
For instance, you can modify your Load Script in this way:

[SalaryTable]:
Load * Inline [
EmployeeID,Hour1,Hour2,Salary
1,-5,0,500
1,5,0,250
2,0,1,700
3,0,0,500
4,2,0,250
4,3,0,700
5,5,0,500
5,-2,-1,250
5,-3,1,500
6,2,0,250
6,-2,0,700
];


[CalculatedSalary]:
Load
    EmployeeID,
    TotalSalary
Where
    (TotalHour1 + TotalHour2) = 0
;
Load
   EmployeeID,
   Sum(Hour1) AS TotalHour1,
   Sum(Hour2) As TotalHour2,
   Sum(Salary) AS TotalSalary
Resident
	[SalaryTable]
Group By
	EmployeeID
;

That does the magic and your chart expression can be a simple Sum(TotalSalary)  or even just the field TotalSalary if you don't need to show a chart total.

Salary.GIF

Note: I'm just not sure about EmployeeID 5 salary.

 

Regards,

Mark Costa

 

vidamakiling
New Contributor II

Re: Expression within a set analysis

must be a typo! thanks for pointing that out.

vidamakiling
New Contributor II

Re: Expression within a set analysis

thank you so much! this is really good to know. appreciate your help! 🙂
pradosh_thakur
Honored Contributor II

Re: Expression within a set analysis

Hi

 

I understand that if something can be done in the script we should do that but i don't think we should avoid set analysis always. Creating different tables foe every situation like this which can be if n numbers will slow down the script and will make the data model complex and hard to understand. This case i will prefer the UI where . if there is any case where  expression is really slowing down the dashboard i would create a flag in the data model in the same table for the situation and use set analysis like <flag={1}>.

This is just my two cents and not to tell what is right and what is wrong. If my take is wrong i hope some MVP of the community can guide us on this. @sunny_talwar   @rwunderlich

Learning never stops.
Partner
Partner

Re: Expression within a set analysis

Yes, you are right.

Sometimes you can't avoid Set Analysis and removing everything from chart expressions to the Load Script can make your code so complex that will not be worthy.

At the end we have to look for a balance between Load Script and chart expressions.

 

Regards,

Mark Costa