Expression within a set analysis

[vidamakiling]

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!

Thanks in advance! 

[michal__ · Accepted solution]

Hi,

try this:

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

[pradosh_thakur · Accepted solution]

please try below

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


Salary)

[vidamakiling]

thank you so much! this worked!

[marksouzacosta]

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

[marksouzacosta · Accepted solution]

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.

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

Regards,

Mark Costa

[vidamakiling]

must be a typo! thanks for pointing that out.

[vidamakiling]

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

[pradosh_thakur]

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

[marksouzacosta]

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