Calculated Value Filter in Widget Error

I'm trying to use CASE WHEN function in a calculated value to generate a filter for the Pivot Table Widget. This works in a bar chart, but I get an error when I try to use the same calculation and setup in a pivot table. Here are the details.

The goal is to filter the table when cumulative % of total spend is less than or equal to 10%. Here I was able to tag all rows that are below 10% with the value 1. Then I use that to generate a filter "exclude all 0".

HQ_Dev_Prod

Posted 9 months ago·Last reply 8 months ago

6 comments

DRay

·8 months ago

Hi HQ_Dev_Prod,

Were you able to get this resolved?

DRay

·9 months ago

Hi HQ_Dev_Prod,

Were you able to get this resolved?

HQ_Dev_Prod

OP9 months ago

Thank you! I'll share this info with our admin. How did you get to Service Configuration?

DRay

·9 months ago

Hi HQ_Dev_Prod,

It looks like you can access that setting this way. https://docs.sisense.com/main/SisenseLinux/configuration-manager-sisense-documentation.htm

akaplan

·9 months ago · Edited

Hi HQ_Dev_Prod,

I reproduced this on an internal machine and found the specific error message:

OVER: only possible within the selection

Since this relates to Window functions, I looked into Sisense's configuration and found this translation setting:

applyMeasureGrouperOptimizationForWindowFunction

It appears that it's intended to optimize or safeguard what might otherwise be highly resource-intensive formulas involving window functions. Disabling the toggle alleviates the error. I searched internally for further documentation on this config and haven't found anything so far, so I can't guarantee how else this might affect your deployment.

DRay

·9 months ago

Hello HQ_Dev_Prod,

Thank you for reaching out. I see your question hasn't gotten a response yet, so I'm asking internally to try and get you an answer.