Return Letters in a Front End Calculation

Hello good afternoon!

I would like some help or if it would be possible to create a calculation that would return a letter for a given situation.

Example:
if sum(d-1, 0, m1) >0 then A else 0

Only it does not return the letter A that was mentioned in the calculation.

I would like to know how I do.

0

Comments

2 comments
  • Hi Willer

    You can't return a letter as a result of a calculation - but with a small workaround, you can make it work anyhow using the number formatting.

    Here's a table where I want to return an F if Profit Margin is less than 60 and maybe an A if it's above or equal to 60.

    My calculation syntax looks like this:
    if sum(d1,0,m1)>= 60 then 1 else -1

    Now my table has a calculated column (Grade):

    Now I go to the number formatting in the properties section and choose to work with my calculated column (Grade):

     

    Having picked Grade, I chose Advanced format:

     

    In the Advanced dialogue, you can choose different formatting for positive and negative numbers:

    This is also shown add a formatting mask: ##0;"-"##0

    The first part (until the semicolon) describes formatting for positive numbers, and after the semicolon, it's targeted at negative numbers

    You can translate the mask into:
    positive numbers with at least one digit;
    negative number with a leading - sign and at least one digit

    Now I manipulate the format string to this:

    "A";"F"

    That means leading A for positive numbers (and no digits) - leading F for negative numbers (and no digits).

    The result:

     

    Hope it makes sense

    br/Niels

     

     

    1
  • The only way to handle this properly at this moment is to add a calculation in the underlying model and return the correct "character" there.
    Eg in DAX 

    Measure = if([Score] = 0, "A", if([Score] = 1, "B", if([Score]=2, "C", if([Score]=3,"D","Z"))))

    If you add the measure in your Targit object, you will now have the desired characters visible in the object as measure.
    You will only be able to use this measure in crosstabs and tables.

    Niels Thomsen, I think what is lacking in Targit (and what this user needs) for measures in crosstables/tables is the same functionality as you have in the properties pane of a chart object. You can add custom labels/tooltips using the "advanced editor" and add a "conditional text" to the "chart element" or in other words the underlying "measure". 
    So take the above example and turn it into a bar chart, you could have the grading with characters displayed in the chart using conditional text but this is not possible on cross tables. 

    1

Please sign in to leave a comment.

Didn't find what you were looking for?

New post