Filtering a chart but keeping an unfiltered value without breaking filters

[oriens]

Hello,

I imported a number of data sources and created a table widget of 3 dimensions (employee name, worked hours, year), and created an "year" filter that will filter the data set according to the worked hours for each years.

Now, I'd want to create a new"total hours" column to show the number of total worked hours for each employee for every year that would be equal to "worked hours" without a filter, and equal to the unfiltered value if an year filter is selected, so I tried to put in a new "total hours" column the formula: 

    sum({1}Fieldname)

but it will break all table filtering, while the expectes result would be:

- to keep the "total hours" field show all the hours that have been worked by an employee apart from the "year" filter that will affect the "worked hours" field (and show only the employee rows that have worked in the selected year, as it already does)

- to keep all the column filters working.

Any help is greatly appreciated.

[pedrobergo]

Hi @oriens 

You can use Set Analysis direct like that:

Sum({1<Employee={"=Sum(Fieldname)>0"}>} Fieldname)

A more simple approach is using an IF statement to check the actual sum avoiding showing all employees and not only for those have hours on selected year, like it.

If(sum(Fieldname)>0, sum({1} Fieldname) )

But the null values will still showing for zero values, so you must uncheck the Include zero values on properties panel.

[],

Pedro