Using Calc Column as filter on Pivot Table Not Working

Question & Answer
,
1

Hi all. I had a simple ifelse() calc column on a pivot table that I used as a filter (was not included in the field wells). It worked a week or two ago, but it is not coming back as Ingestion Failure.
Here’s the calc:

ifelse(
    sum({proj_lysol_activity_ct})>0,'Y','N'
)

123tester

Is this due to the recent update? I’m at a complete loss as to why this suddenly broke

2

I would suggest moving it to a custom filter list and hard coded ‘Y’ , ‘N’

image
image484×868 20.3 KB

3

I didn’t mention this will be a filter ON the sheet as well. Ideally, it’d be a drop down with Y or N or Select All. Using the Custom Filter (not the list one) works if the users enter ‘Y’ or ‘N’, but this will absolutely cause headaches in the long run and is not - at all - user friendly. Especially with new users who don’t know how to reset the filters or reset the enter page.

4

Got a solution:
(1) Created a calc field with an ifelse() statement with ‘Y’/‘N’ like:

ifelse(
    sum({_activity_ct})>0,'Y','N'
)

(2) Created a parameter with the dropdown values I want. like:
‘Has Activity’
‘Include All’
(3) Created another calc field with a final ifelse() statement that is used as a filter on the table (and not added to the sheet):

ifelse(
    ${ynparam}='Has Activity' AND {_haswklygoal}='Y',1,
    ${ynparam}='Include All',1,
    0
)

The filter is set to 1 and this works great thus far.

5

Nice!

Thanks for posting the solution

1 Like