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

vguddanti
vguddanti Member
edited October 2023 in Magic ETL

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

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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))

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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))

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • vguddanti
    vguddanti Member

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

  • GrantSmith
    GrantSmith Coach

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories