Have some filters apply to my calculated field but not others

Question & Answer
, ,
1

Hi all - I have a use case where I want my calculated field to take some filters, but not apply to others.

I am trying to show how resources are aligned to work. We have 2 tables showing this data, where the top table shows a ‘summary view’ of the data that is unfiltered. The bottom table shows the same data, but has selectable filters that allows for further drill downs.

When unfiltered, both tables show the same data (this is what is expected). However, when we filter the bottom table to show only a subset of the top table, the numbers do not align. Example below:

Top Table Capacity
Group A 5.5
Group B 2.5
Group C 1

Bottom Table
Group B 3

Ideally, we would want the Group B value in the Bottom Table to be 2.5. The reason for this is because the capacity calculation we have currently calculates after all filters are applied. We need the capacity calculation to calculate after certain filters (date ranges for tickets) but after other filters (Group in the example above). I found this explanation which is similar to my use case, although what we need to filter on is not an integer (ex: Group B in the above), and we need the ability to select multiple groups.

Any help (in the form of solutions or letting me know if its not possible) would be much appreciated!

2

Hello @guhifdgh !

Can you share the calculated field that is not working correctly?

Any filters you want it to check for should be parameters. Then you can check the parameter values in a calculated field. Then you can use PRE_FILTER to ignore the other filters if you utilize LAC-W aggregations

3

Hi Duncan! The calculated field is pretty complex, I will link it below.

ifelse(
ifelse(
ifelse(
sumOver({POINTS}, [{Resource_ID}], PRE_AGG) = 0, 1, 0) = 1, 'Use Count',
sumOver({is_points_filled}, [{Resource_ID}], PRE_AGG) / countOver({ISSUE_KEY}, [{Resource_ID}], PRE_AGG) >= .75, 'Use Points', 
'Use Count') = 'Use Points', 
            	ifelse(isNotNull({POINTS}), {POINTS}, 0) / sumOver(ifelse(isNotNull({POINTS}), {POINTS}, 0), [{Resource_ID}], PRE_AGG),
1/(distinctCountOver({ISSUE_KEY}, [{Resource_ID}], PRE_AGG))
       )

If I understand correctly, I should change all the ‘PRE_AGG’ to PRE_FILTER, then utilize parameters as a calculated field, and filter on the calculated field to get my desired result?

4

Hi @duncan - any update on this?

I updated my formula above to reference all as ‘PRE_FILTER’, and created a calculated field that checks my date field. I linked this calculated field to a parameter and filtered on that, but when filtering on the dates the numbers I am getting are smaller than they should be.

It seems like the calculation is occurring before the date filtering, but I need the calculation to run after the date filtering and before my other filters in my analysis.

Thank you in advance for the help!

5

Hi @guhifdgh,
Noticing that it’s been a bit since last communication, are you still having issues with your original topic or were you able to find a work around solution?

Let us know if you have any remaining questions or if this topic is clear to be closed out.

Thank you!

6

@Brett We found a workaround but it required using multiple datasets and limited the filters we could apply.

Do you know how we could alleviate our original issue via a calculated field or other means that does not include multiple datasets?

7

Hi @guhifdgh,
Glad to hear you were able to find a workaround. I tried a couple things to test out but from my understanding, there’s no simple way to achieve this through a calculated field at the current time.

Thank you!