Variable names in String

LearningNinja

Hi,

The current variable selector accepts the values either in

• Default (Numeric) - Gives a comma separator automatically.
• Percentage
• Currency - Adds $ as prefix.

Do we have any work around to avoid comma?

Do we have any work around to use other currency symbols as prefix with currency format?

jessdoe

variableYear Total:

sum(case
when date_format(insert_the_name_of_the_date_column,'%Y') = variableYear
then insert_name_of_measure_you_want_summed

end)

variableYear - 1 Total:

sum(case
when date_format(insert_the_name_of_the_date_column,'%Y') + 1 = variableYear
then insert_name_of_measure_you_want_summed

end)

@LearningNinja

ColemenWilson

You can change the currency prefix for the field by clicking the V next to your field > Format > $ > select the currency prefix you prefer. If the $ dropdown does not contain the currency symbol you want, you can enter your desired symbol in Chart Properties (Number Format > Currency Symbol).

For removing the comma, what is your chart type? And do you want the comma replaced with a "."? Or just gone altogether? Are you doing any aggregation first?

LearningNinja

@ColemenWilson - I have created a variable > dropdown for Year with a list of values : 2024, 2023,2022,2021.
Displayed this variable on a card on the dashboard by Creating a card > Insert a variable.

Now, the dropdown on the dashboard shows the values with comma.

Also, why did I use this variable in first instance is, to perform a previous year calculation like,

If user selects 2024 on UI, i want to perform a math such that,

CASE WHEN Year= var_Year - 1 THEN SUM(Sales) ⇒ This will give me the sum of sales for prev year.

I will display my Current Sales and Prev Sales in a multi value column chart or on few bar graphs as well.

ColemenWilson

@ArborRose had this exact problem, here is his solution:

https://community-forums.domo.com/main/discussion/58361/format-as-number-without-comma

LearningNinja

@ColemenWilson

I have gone through the post, but it talks about converting a numeric column to a string.

However, when setting a variable, how do we do that? I do not see any option do perform anything.

This is the only window I have to edit its configuration:

ArborRose

In the post @ColemenWilson is referencing, the optional group by is the second value. I was formatting the first dropdown and not looking at format on the second dropdown.

LearningNinja

@ArborRose - Can we quickly connect once? If it is possible, please.

ArborRose

@LearningNinja, what is available in the dropdown labeled "Format" at the bottom of your screenshot? Is there an option for thousands comma selector there?

LearningNinja

@ArborRose Default, Percent and Currency.
Checked through all three options but no luck to handle it.

ArborRose

Instead of using the default numeric format, can you create a calculated field (or beast mode) to convert the number to a string without commas? Use a formula like CONCAT('', number) to ensure the number is treated as a string, which typically won't have commas.

CONCAT('', ROUND(number_field, 0))

CONCAT('$', ROUND(number_field, 2))

CONCAT('£', ROUND(number_field, 2))

LearningNinja

But, I am unable to use it in formulas. I think, I am unable to put the issue clearly here.

The main purpose of using YEAR as variable is to use in the formula to perform -1 of it.

jessdoe

Does selecting "Text" in the Type of Values section do the trick?

david_cunningham

@LearningNinja in the example I provided for a different question yesterday, I showed you how you could have year display without comma, but still be used to get the previous year value.

You'll need a combination of restructuring your data in an ETL, and a beast mode to generate your year filter.

See my answer on the most flexible way to restructure your data here -

https://community-forums.domo.com/main/discussion/comment/96333#Comment_96333

CONCAT(YEAR(compare_date))

Above is a beast mode that will remove the comma. These 2 together will let you select a year in a filter card, and then have the previous year value from the year selected show as compared to the year selected.

You can expand this out to have different comparison periods, and allow the user to select those as well (previous year, previous month, previous 12 weeks, etc).

LearningNinja

Thanks for the response, @david_cunningham. Is there any possibility without using ETL. Due to limitations, I am looking for solutions without ETL and limited to beast mode. Also, I am looking for a more dynamic solution where the user may select any Year from the dropdown which would become my base/current year and the previous to it is my previous year.

Yes, you are right, I will be doing comparison with same periods with prev. year.

LearningNinja

@jessdoe - Yes, that removes the comma but on the Year, I need to perform a Year - 1.

What I have performed is:

Set up a variable > Text > 2024,2023, 2022. ⇒ Variable name: variableYear

In Beast mode, multiplied variableYear*1 which converted the value to integer.

Now, i am looking to perform a sum based on year. Strangely, the values are displayed on a HTML Table but not on a multi-value column card.

jessdoe

variableYear Total:

sum(case
when date_format(insert_the_name_of_the_date_column,'%Y') = variableYear
then insert_name_of_measure_you_want_summed

end)

variableYear - 1 Total:

sum(case
when date_format(insert_the_name_of_the_date_column,'%Y') + 1 = variableYear
then insert_name_of_measure_you_want_summed

end)

@LearningNinja