Add a condition on line plot to toggle between AVG and SUM conditions

vguddanti

Hi,

I have a column in my data 'Volumes' and I am using that as Y-axis of line chart which is currently set to aggregate as SUM(Volumes). I have a problem with it where I want it to perform AVG(Volumes) when one condition is met and perform SUM rest of the times.

I am trying to perform this logic using calculated field, but when I drag the field in to the visualization, it says that an error has occurred. Please suggest any recommendations I can try to resolve the issue.

Here is the Beast mode calculation I am trying:

CASE WHEN COUNT(Plant, Group, Code, Month, Name, Part Number1, Part Number2)>1
THEN AVG(Volumes)
ELSE SUM(Volumes)

END

GrantSmith

The issue is with the count portion, are you looking for distinct row counts you can CONCAT all of the fields together:

COUNT(CONCAT(Plant, Group, Code, Month, Name, Part Number1, Part Number2))

MarkSnodgrass

It is likely throwing any error because of how you are trying to use the count function. Are those individual fields within your dataset? The count function just takes one field: COUNT(plant) . If you are looking for any data in any of those fields, I would use the COALESECE function inside the COUNT function and list all of the fields inside the COALESCE function.

GrantSmith

The issue is with the count portion, are you looking for distinct row counts you can CONCAT all of the fields together:

COUNT(CONCAT(Plant, Group, Code, Month, Name, Part Number1, Part Number2))

vguddanti

I think that worked well with CONCAT function. I dont see the error anymore. Thanks!

GrantSmith

One other thing, if you want to see the unique count you can add a DISTINCT to the count before the CONCAT