# Convert FIXED CASE WHEN tableau calculation to QS

Hi QS Community,

I have a LOD calculation in tableau which I’m trying to convert to QS but no luck. Would appreciate any support!

Tableau calc:

CASE [Country]
WHEN ‘US’
THEN { FIXED [field_1], [field_2], [field_3], [field_4]: COUNTD([Id])}
ELSE
{ FIXED [field_1], [field_2] : COUNTD([Id])}
END

tried:

ifelse(

{country} = ‘US’,

distinct_count({id}, [ {field_1}, {field_2}, {field_3}, {field_4}])

, distinct_count({id}, [ {field_1}, {field_2}])

)

Also tried:

distinct_count(

ifelse(

{country} = ‘US’, {id}, [ {field_1}, {field_2}, {field_3}, {field_4}]

, {id},[ {field_1}, {field_2}]

)

)

@Koushik_Muthanna , @Kristin – If any of you could please help or point me to the right direction!
Appreciate your support for the QS community.

Is FIXED the same as over?

Does this work?

ifelse(

{country} = ‘US’,

distinctCountOver({id}, [ {field_1}, {field_2}, {field_3}, {field_4}],PRE_AGG)

, distinctCountOver({id}, [ {field_1}, {field_2}],PRE_AGG)

)

2 Likes

Hi @jemin. Thanks for reaching out. Let us know if @Max’s solution works for you.

Thank for responding @Max … your solution lets me create a cal field… but when I pull country and this calc field in the view just to check initial values, I get an error saying you calc field expression contains invalid syntax.

calc field:

ifelse(

country = ‘US’

, distinctCountOver({member_id}, [{survey_name}, {Title - Updated}, {Season - Updated}], PRE_AGG)

, distinctCountOver({member_id},[{survey_name}, {Title - Updated}], PRE_AGG)

)

P.S. I also tried adding the partition by fields to the view, still getting the same error

okay, I figured it’s happening because of field Title - Updated and Season - Updated.

Title - updated references survey_name, and
Season - Updated references both survey name and title - updated…

But, they’re just string manipulation. So, why would it affect the calculation?

1 Like

@jemin Glad you figured out what happened. @SeanBoon @emilyzhu Any thoughts on why this might have affected the calculation?

Sorry to overturn your solution marking @Kristin . I was able to figure out why @Max 's calculation was showing syntax error but still haven’t found the solution to the original problem.
As the fields are unusable in the formula

Hi @jemin All good. Thanks for clarifying that the original problem still persists.

Hi @jemin,

can you try to include {country} as a partition field and see if this solves the issue? Like

ifelse(
country=‘US’,
distinctCountOver(id, [country, field_2, field_3, field_4], PRE_AGG),
distinctCountOver(id, [country, field_2], PRE_AGG)
)

If it does not solve your problem, can you please send us a screenshot with the calculated field you’re using, the visuals (in both Tableau and Quicksight) and details of the error? and if possible, a screenshot of the original data that has the fields in questions. We believe it must be something with the way the data is structured. The MIN as the aggregation for the new calc should not work either…

1 Like

Hi @gillepa ,

Thank you for responding. Through trial and errors I figured that even after applying Max’s syntax i was getting error in the view because of the string manipulation calculated field.

I also found a post where someone had similar issue. Basically, I cannot use a calculated field as a partition by field in a separate calculation… I need to move that calculation to the data set layer, which is kinda annoying and time-consuming.

Hi @jemin, I tried to reproduce the exact same situation you have using a similar formula (see below) and it worked. I got the results I expected:

ifelse(
Country=‘United States’,
distinctCountOver(Customer, [Country], PRE_FILTER),
distinctCountOver(Customer, [Country, Industry, Status], PRE_FILTER)
)

Can you please give it one more try?
Thanks!

1 Like

Hi @gillepa ,

Thanks again for following up… Are any of the fields [Country, Industry, Status] calculated field created within QS?
Because when I try using calc field it surely gives error.

Hi @jemin,

only one field is a calculated field at the data set level (that is, before you publish the dataset to visualize it).

I hope it helps.

1 Like

ohh! Thanks for informing @gillepa . In my case I had 2 calculated fields after publishing the dataset to visualize…that is where the issue is happening

Happy we identified the issue!
Let us know if there is anything else we can do to help.

1 Like