Variable names in String

Options

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?

Best Answer

  • jessdoe
    jessdoe Contributor
    Answer ✓
    Options

    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

Answers

  • ColemenWilson
    Options

    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?

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

  • LearningNinja
    Options

    @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
    Options

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

  • LearningNinja
    LearningNinja Member
    edited June 4
    Options

    @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
    Options

    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.

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

  • LearningNinja
    Options

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

  • ArborRose
    Options

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

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

  • LearningNinja
    Options

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

  • ArborRose
    Options

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

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

  • LearningNinja
    Options

    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
    jessdoe Contributor
    Options

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

  • david_cunningham
    Options

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

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

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • LearningNinja
    Options

    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
    Options

    @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
    jessdoe Contributor
    Answer ✓
    Options

    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