Unable to save the card with this beast mode.

MayaU_01
MayaU_01 Member
in Beast Mode

In my data, I have multiple months, A subcategory, a commitment column, goal, actual, and a format column.

I want to replicate a table where all subcategories, commitment, actual, and goal are listed.

My goals and Actuals are just numbers, their formats are discussed in a separate column, so if the format column says it is M, then I consider my value in a million and if it says Percent, then i consider a % next to it.

In my requirement, I have a table where commitment is listed in a single column and in this case my goals and actuals will be a mix of formats, some values will be a currency, percent and some will be a number. so I used a formula to where I am using concat to help fix values. It works till the time i am only using one month of data in the date selection.

If all time range is selected, all months get listed in the table, and then if multiple months have Q1 value, then in the commitment column i see Question number 1 mentioned multiple times. I tried to average out the actual value so i can only see commitments listed once and then values next to it change according to month selection. But when I use Average aggregation in Actual column then My concat functions don't work and values are just flat INT values. so It becomes difficult to identify the format.

I tried to use this below formula, to fix the issue, by this I can solve the values and can use any date range and have commitment listed only once, but then i am not able to save the card, it gives error that unable to save card. Please help.

Case when format='Percent' then CONCAT(ROUND((AVG(Actual))*100,2),'%')
when format='M' then CONCAT(ROUND((AVG(Actual))/1000000,2),' ','M')
when format='Mdollar' then CONCAT('$',ROUND((AVG(Actual))/1000000,2),' ','M')
when format='K' then CONCAT(ROUND((AVG(Actual))/1000,2),' ','K')
when format='#' then AVG(Actual)*1
END

Attached is my sample data

ESG _ goals _ Test.xlsx

Answers

  • ColemenWilson
    ColemenWilson Coach

    A few questions:
    1. By Q1 do you mean question 1 or quarter 1?
    2. If you mean Quarter 1, is this a new calculated field? Because it isn't in your data
    3. If you are aggregating there shouldn't be duplicates unless you have sorting applied which can cause duplicates even using aggregates.
    4. I used your data and beastmode and am not having any issues even when selecting for a single month:

    Screen Shot 2023-11-20 at 2.48.32 PM.png

    If I solved your problem, please select "yes" above

  • GrantSmith
    GrantSmith Coach

    This is because you're attempting to compare a single row value (format='Percent' for example) while then attempting to aggregate your entire dataset with the AVG. Domo can't interpret this so it's causing an error when attempting to save the card.

    Another issue I see in your data - if you look at Question Number 1 there are multiple different formats. So which format should be used for Q1 if you have Jan and Feb in your data? # or Percent?

    Depending on the number of questions you have you may want to pivot your data so that each question is it's own column which you can control your formatting easier with.

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

    What is the final goal of your analysis? Do you want to have a table that summaries all time goals by subcategory and question number? If so, do you want to add the all-time goals or average them?

    I suggest converting each goal into a common format first, if there will be an aggregation across months.

    However, if the analysis does not require aggregation, and only formatting is necessary, then the code you provided will work if you remove the AVG() functions from each of the when statements.

    Alternatively, you can multiply the values by 1000 or 1,000,000, etc. depending on the format character, then have the table abbreviate the final number so that the K and M appear on the visual, but the numeric values are still preserved in the table. This is very useful if you will be exporting the information to a spreadsheet.

  • MayaU_01
    MayaU_01 Member
    https://community-forums.domo.com/main/discussion/comment/82785#Comment_82785

    Got the first part of the message. Thanks. I will check another way to it.

    Seems like a miss - the Q1 is a percent.

    I tried to use pivot, but the HTML table view is much cleaner which is preferred by the client. Any other suggestions.

  • MayaU_01
    MayaU_01 Member
    https://community-forums.domo.com/main/discussion/comment/82784#Comment_82784

    by aggregating i can fix the issue of duplicate value of commitment while multiple months are selected, but then the beast i am using to concat the % will not work.

    My bad, I dont have any quarter in data, its just Questions

  • GrantSmith
    GrantSmith Coach
    https://community-forums.domo.com/main/discussion/comment/82789#Comment_82789

    You can pivot your data in an ETL first and then display it with an HTML table.

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

    I want a table, where First column gives infor for sub category, second gives all Commitments and then in the next columns I do have my actual and Goals.

    My actuals and goals are mixed format for this type of arrange ment, As per my requirement i cannot seperate this group of questions, because i cannot use more than one cards or seperation to show the same group of formats.

    so i used the beast mode to add the formats as it is. but if i want to keep the formats i have to select no aggregates and then i get multiple month entry of same questions thus showing two actual values.

    If i agrregate which solves my problem of duplicate commitment values then i loose my concat values of % and other formats.

    I want to show the overall average of the actuals and goals. So if anyone selects just one month, i can have the right value and with formating symbols like % and K and M, $ and if i select multiple months, then still without duplicating my Commitments i get a averaged value of the same.

  • MayaU_01
    MayaU_01 Member
    https://community-forums.domo.com/main/discussion/comment/82793#Comment_82793

    @nmizzell

Categories