# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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:

 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

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:

 EmployeeID Hour1 Hour2 Salary 1 0 0 750 3 0 0 500 5 0 0 750 6 0 0 950

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!

Labels (3)

• ### sum

3 Solutions

Accepted Solutions
Highlighted
New Contributor II

## Re: Expression within a set analysis

Hi,

try this:

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

Honored Contributor II

## Re: Expression within a set analysis

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

Salary)```
Learning never stops.
Contributor

## 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.

```[SalaryTable]:
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]:
EmployeeID,
TotalSalary
Where
(TotalHour1 + TotalHour2) = 0
;
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.

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

Regards,

Mark Costa

22 Replies
Highlighted
New Contributor II

## Re: Expression within a set analysis

Hi,

try this:

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

Honored Contributor II

## Re: Expression within a set analysis

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

Salary)```
Learning never stops.
New Contributor II

## Re: Expression within a set analysis

thank you so much! this worked!

Contributor

## 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

Contributor

## 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.

```[SalaryTable]:
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]:
EmployeeID,
TotalSalary
Where
(TotalHour1 + TotalHour2) = 0
;
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.

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

Regards,

Mark Costa

New Contributor II

## Re: Expression within a set analysis

must be a typo! thanks for pointing that out.

New Contributor II

## Re: Expression within a set analysis

thank you so much! this is really good to know. appreciate your help! 🙂
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.
Contributor

## 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