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)
)
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?
@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,
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…
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!
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.
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.
Let us know if 