Replicate IF(SUM(fieldA)>0, SUM(fieldB)) with set analysis

[smiling_cheetah]

Hi everyone,

Sorry if this is repeated question, but I couldn't find a related topic and would appreciate any help with this one.

So I got a table looking like following

T:
Load * inline [
date, agent, type, goal, act
01.01.2019, A, 1,, 120
01.01.2019, A, 2, 100,
02.01.2019,	A, 1,,150
02.01.2019, A, 2,,90
01.01.2019, B, 1,, 0
01.01.2019, B, 2, 120
02.01.2019, B, 1,, 0
02.01.2019, B, 2, 140
];

The ultimate goal is to create a bar chart:

dimension=agent

measure1=sum(act)

measure2=sum(goal)

But to show only those agents, which have their sum(act)>0.

(Thus the chart will not show agent=B)

The basic solution I could find is to turn off zero values in chart settings and modify:

measure2=IF(SUM(act)>0, SUM(foal))

But can the same effect be achieved by set analysis (to get rid of IF clause)? 

Thanks in advance

[tresesco · Accepted solution]

Try this

Sum({<agent={"=Sum(act)>0"}>}goal)

[smiling_cheetah]

Thanks!
Works as intended 🙂