SUM(distinct) Fixed By

Viswaja
Viswaja Member
edited February 29 in Beast Mode

I have a column that needs to be a constant value based on two more columns. So I am using this formula
SUM(DISTINCT Balance) FIXED (BY Client, Item). This is working fine.
However, When I am using the same formula in another beast mode I am getting the below error

(1-SUM(Lost))/ sum ((SUM(DISTINCT Balance) FIXED (BY Client, Item)) + Received)

An invalid column was specified in your request (the column does not exist on the DataSet). 

However , all the columns exists in the dataset and the beast mode is saving fine but when I use it in any card I am getting the error.

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    https://community-forums.domo.com/main/discussion/comment/90770#Comment_90770

    The Fixed function is returning a single value for each row and you're adding that to an aggregation across your entire dataset so it doesn't know how to handle this. You'd need to aggregate your fixed function again so that you can add the two together.

    SUM(SUM(DISTINCT Balance) FIXED (BY Client, Item)) + SUM(Received)

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

Answers

  • ArborRose
    ArborRose Coach

    I had this same thing happen to me last week. It turned out I miss typed single quotes rather than a back tick. I would have suspected order of operation but your parenthesis look right to me.

    Remove the space after the second occurrence of sum.

    Check column names for spelling and back tick quotes. If your first worked, I would focus on Received and Lost.

    Check for hidden characters. To filter properly, copy the whole string to notepad and then copy from notepad back again. Notepad is dumb. It doesn't know how to do an extended or hidden character.

    Verify data types.

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

  • Viswaja
    Viswaja Member

    Thank you for the answer I did check everything but it is still not working for me.

    I changed the formula to this and it is showing me a different error now.

    (SUM(DISTINCT Balance) FIXED (BY Client, Item)) + SUM(Received)

    An issue has occurred during processing. We are unable to complete the request at this time. 
    Is there anything I am missing here?

  • ArborRose
    ArborRose Coach

    Perhaps there's another way to approach the problem. Can you provide a few sample rows of test values and a description of what you need?

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

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    https://community-forums.domo.com/main/discussion/comment/90770#Comment_90770

    The Fixed function is returning a single value for each row and you're adding that to an aggregation across your entire dataset so it doesn't know how to handle this. You'd need to aggregate your fixed function again so that you can add the two together.

    SUM(SUM(DISTINCT Balance) FIXED (BY Client, Item)) + SUM(Received)

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

    Thank you both so much.

    GrantSmith's fix helped me.

Categories