8 Replies Latest reply on May 1, 2018 12:36 PM by Jacob Goffin

    Dynamic Text

    elizabeth.rathburn

      Is there a way to make dynamic text based on if a number increases or decreases? So if the number increased from last month I want to say "Total sales increased by <monthly change> in <current month>." Or if it decreased I want it to say "decreased by". I can't attach a packaged workbook because it's confidential information. I have the monthly change and current month already figured out, I just don't know how to do the other part. Any help is appreciated!

      Thank you!

        • 1. Re: Dynamic Text
          Jacob Goffin

          Hi Elizabeth,

           

          The simplest option would be to change the wording to "Total sales changed by <monthly change> in <current month>." and have the positive/negative value of monthly change indicate if it is increasing/decreasing. But, if you want to be able add that dynamic wording you should be able to with a calculation. I've used Superstore to demonstrate example, writing a calculation "Dynamic Text"

          that has the structure:

           

          IF [Monthly Change] >= 0

          THEN "increased by"

          ELSE "decreased by"

          END

           

          The result is the snapshot below (and see attached workbook as well), where I've put the dynamic text in the tooltip.

           

          dynamic text.png

           

          Hope this helps.

           

          Cheers,

          Jacob

          1 of 1 people found this helpful
          • 2. Re: Dynamic Text
            Jim Dehner

            Hi Elizabeth

             

            I will assume you have made the appropriate calculation

             

            so yes you can create an IF then statement something like

             

               label =       If [monthly change] > 0 then " increased by ' else ' decreased by' end

             

             

            Then you other statement (could be in a tool tip)     =      'Total sales' + [label ] + 'by ' +str([monthly change]) + ' for the month ' + str([current month])

             

            You may need to play with the syntax depending on the calculations you used and the actual value of the change may need to be rounded

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: Dynamic Text
              Ivan Young

              You will need  datevalue or datepart, str and IF functions and perhaps some LOD but it is possible.

              • 4. Re: Dynamic Text
                elizabeth.rathburn

                Thank you Jacob! This did work.

                Now, what if the number did not change? So [Monthly Change] = 0.

                 

                Thank you,

                Elizabeth

                • 5. Re: Dynamic Text
                  elizabeth.rathburn

                  Jacob Goffin - I figured out what to do if the number did not change, but now I have another question.

                  So this is my calculation now for my dynamic text:

                  IF [Monthly Change]>0 THEN "increased by"

                  ELSEIF [Monthly Change]=0 THEN "did not change"

                  ELSE "decreased by"

                  END

                   

                  Now my dilemma is the way I have my sentence written after "increased or decreased by" the actual monthly change number is there. But when there is no change and "did not change" is written I don't want that number to be there. My hope is that this is going to be automated and I won't have to go in every month and change the way the sentence is written. Is this possible to do?

                   

                  Much appreciated!

                  Elizabeth

                  • 6. Re: Dynamic Text
                    Jacob Goffin

                    Hi Elizabeth,

                     

                    In that case it may be easiest to just write the whole sentence within the calculation, rather than leaving part of it to be written in the tooltip. That way you have complete control for the three possible cases. Something like:

                     

                    IF [Monthly Change]>0 THEN "Total sales increased by " + STR([Monthly Change]) + " in " + STR([Current Month])

                    ELSEIF [Monthly Change]=0 THEN "Total sales did not change in " + STR([Current Month])

                    ELSE "Total sales decreased by " + STR([Monthly Change]) + " in " + STR([Current Month])

                    END

                     

                    Cheers,

                    Jacob

                    • 7. Re: Dynamic Text
                      elizabeth.rathburn

                      Jacob Goffin That's a great idea! Of course it's easier said than done for me. I'm using Max Date instead of Current Month so I will always have the most recent date, but I'm getting errors in my calculation for using string and integer values. Then when I change it to STR I get an error for mixing aggregate and non-aggregate arguments so I'm not sure how to write the calculation using Max Date.

                       

                      Thank you!

                      Elizabeth

                      • 8. Re: Dynamic Text
                        Jacob Goffin

                        Hi Elizabeth,

                         

                        Those are both good points. That's what I get for trying to write the calculation in my head without actually testing it.

                         

                        Now that I think about it a bit more, it's hard to control formatting if you include multiple fields in the text output of a calculation. Therefore, we could try another approach: create a calculated field based on [Monthly Change] that makes it null if the value is zero. (I've called it Monthly Change - Tooltip in my workbook). If a measure value is NULL, by default it will appear blank in a tooltip.

                         

                        IF [Monthly Change] = 0 THEN NULL

                        ELSE [Monthly Change]

                        END

                         

                        See the attached workbook for an example.

                         

                        Cheers,

                        Jacob