12 Replies Latest reply on Oct 27, 2017 8:24 AM by Jonathan Drummey

    Email Body in Measures don't work

    Martin Ocando

      I have a viz about our DW DB usage, and I created a couple calculated fields, to measure our daily usage of disk space, and the amount of days until it will reach 90%. The fields are like this:

       

      For daily usage, the formula is: (MAX([used])-MIN([used])) / DATEDIFF('day',MIN([sysdate]),MAX([sysdate]))

      And for days until 90%: ([Capacity]*.9)/[Usage per Day]

       

      Now, the Email Body field is like this:

       

      '<font size ="4">Space analysis indicates that, at the current rate of <bold>' + STR([Usage per Day]) +

      '</bold> bytes per day, the database will be at 90% of used capacity in <bold>'+

      STR([Days until 90%]) + '</bold> days <br /><br />' +

      'VIZ_IMAGE(DiskSpaceAnalysis/PostGresDBAnalysisforEmail)'

       

      But the moment I do this, it jumps to the measures, and even though is in the Details drawer, it won't  work. It will show an empty email body, with just the standard footer.

       

      Any ideas how can I make this work?

        • 1. Re: Email Body in Measures don't work
          Matt Coles

          There's no issue with using Email Body as a Measure. I have several VizAlerts that do this very thing, because I want some info in there from aggregated fields. They work fine.

           

          I would recommend pulling down the raw CSV data for your alert by navigatoing to the viz in Server, then tacking ?:format=csv to the end of the URL (strip off the "?:iid=#" part first). Then check the value that VizAlerts is seeing. A problem that can arise is that a single NULL value in any of the components of the string you are building will result in the entire string becoming NULL. I wonder if that same problem is occurring here.

          • 2. Re: Email Body in Measures don't work
            Martin Ocando

            Yikes, it is null. The problem is that if I use STR to turn a measure into a string, it shows null. And I tried using STR(ZN([Usage per day])), and it works, but it shows zero.

             

            Any ideas how can I cast a float as text, and show correctly instead of a null?

            • 3. Re: Email Body in Measures don't work
              Matt Coles

              I use IFNULL to replace a null value with a different one. So for me, it's usually just replacing with an empty string:

               

              IFNULL([Possible Null Field], '')

               

              So in your case it might be (depending on what you want to say):

               

              IFNULL(STR([Usage per day]), '')

               

              or

               

              IFNULL(STR([Usage per day]), 'There was no usage today')

               

              ...or whatever.

              • 4. Re: Email Body in Measures don't work
                Martin Ocando

                If you look at the original formulas, the fields are aggregated using the whole dataset, so they are never null. I take the disk usage difference between the first record of the dataset and the last, and divide it by the number of days between the first record of the dataset and the last.

                 

                So, I believe is an issue with the level of detail, but even though I tried to use {EXCLUDE : }, and didn't work either.

                • 5. Re: Email Body in Measures don't work
                  Jonathan Drummey

                  Hi Martin,

                   

                  I suspect this might be a level of detail question as well due to other dimensions in the view. You wrote, "If you look at the original formulas, the fields are aggregated using the whole dataset, so they are never null." That statement is not necessarily true because Tableau doesn't evaluate calculations until the field is used in a view in some way (the simplest being placing the field on a Shelf). So the statement will be true if the field is inn the view and the view has no dimensions on Rows, Columns, Pages, or the Marks Card, or a dimension that has a single value for the entire data set. However if there are one or more dimensions with one or more values on Rows, Columns, Pages, or the Marks Card that have more than one value then the calculation will be aggregated within the dimension values and that can change the results. I'm thinking there are dimensions in the view that are changing the results and causing the calculation to return unexpected values.

                   

                  Therefore I have three requests to help you:

                   

                  1) Can you extract the data source, then export a packaged workbook and post it? If you are unable to do that for confidentiality reasons then hopefully you can respond to the next two requests.

                   

                  2) Can you duplicate your viz as a crosstab? If Measure Names is in the crosstab then make sure that's on Columns with Measure Values on Text. All your other blue pills (like the Email Body pill) should be on Rows and any green (continuous) pills that are measures can be on the Measure Values card. Any other green pills that are dimensions can be converted to discrete pills and placed on Rows. So the view will look something like this (not a VizAlerts view but hopefully you'll the idea):

                   

                  Screen Shot 2017-10-26 at 2.28.36 PM.png

                   

                  Also please add to the Measure Values card the measures for used (i.e. MIN(used) and MAX(used)), capacity, sys date,  usage per day, and days until 90% so we can see what those values are.

                   

                  I pretty much always use views like this to work out and validate calculations and then I duplicate the workout view to build the "real" view. This saves a lot of time in figuring out what calculations are causing the problem.

                   

                  Then can you take a screen shot of this and post it? If necessary use an image editor to block off or white out text that we shouldn't see?

                   

                  3) Can you take a screenshot of your entire alert view, including all Shelves, and post that? Again, edit if you need to.

                   

                  Jonathan

                  • 6. Re: Email Body in Measures don't work
                    Martin Ocando

                    Sure, I can share the workbook, is not really confidential information.

                     

                    And I thought about that too, so I changed the fields to exclude sysdate, which is the only dimension that I'm using, but didn't work either.

                     

                    Anyway, here you go: https://www.dropbox.com/s/ha0je9pz3masfgy/Disk%20Space%20Analysis.twbx?dl=0

                    • 7. Re: Email Body in Measures don't work
                      Martin Ocando

                      Never mind. It works now. I embedded the exclude when calling the calculated fields, and not in them, as I tried before.

                       

                      So the Email Body field ended like:

                       

                      '<font size ="4">Space analysis indicates that, at the current rate of <bold>' +

                      STR({EXCLUDE [sysdate] : [Usage per Day]}) +

                      '</bold> bytes per day, it will be at 90% of used capacity in <bold>'+

                      STR({EXCLUDE [sysdate] : [Days until 90%]}) + '</bold> days </font><br /><br />' +

                      'VIZ_IMAGE(DiskSpaceAnalysis/PostGresDBAnalysisforEmail)'

                       

                      Thanks all for the help.

                      • 8. Re: Email Body in Measures don't work
                        Jonathan Drummey

                        Thanks!

                         

                        Without seeing exactly how you used the EXCLUDE calculation I can't tell you why that didn't work.

                         

                        There are multiple issues problems:

                         

                        1) The vizLOD has sysdate as a dimension (along with the three other single-valued Email dimensions). Therefore the DATEDIFF('day',MIN([sysdate]),MAX([sysdate])) in the Usage per Day calculation is returning 0 because the calculation is evaluated at the level of a single sys date and that returns 0. In addition both MIN(used) and MAX(used) are returning the same value so the (MAX([used])-MIN([used])) also returns 0. 0/0 = Null.

                         

                        This can be seen in this workout view:

                         

                        Screen Shot 2017-10-26 at 3.06.16 PM.png

                         

                        2) Because sysdate is a dimension in the Used vs Avail (Last 72 hr) there are 1698 marks. That would lead to 1698 alert emails (Matt Coles, can you verify this?) when all you want is 1, this is because each mark in the alert view becomes a record in the downloaded csv and each record becomes an alert.

                         

                        I'm not sure what you are wanting, I have a guess based on looking at what you are wanting to send. If I remove sysdate as a dimension from the view then I see one row (it's 10 marks, but that's because of Measure Names/Values that you'd need remove before publishing) and it has the correct values with Email Body not Null.

                         

                        Screen Shot 2017-10-26 at 3.06.41 PM.png

                         

                        v10.4 workbook is attached.

                         

                        Jonathan

                        • 9. Re: Email Body in Measures don't work
                          Matt Coles

                           

                          2) Because sysdate is a dimension in the Used vs Avail (Last 72 hr) there are 1698 marks. That would lead to 1698 alert emails (Matt Coles, can you verify this?) when all you want is 1, this is because each mark in the alert view becomes a record in the downloaded csv and each record becomes an alert.

                           

                          In earlier versions, yeah. In 2.1.0, that's no longer true. We pull only the alert-relevant fields, remove all duplicates, then only send that many emails. So in the above viz you can't tell exactly, but assuming the values visible in the first four columns repeated all the way down, you should only get one email.

                           

                          Having all those records would add additional processing overhead to Tableau Sever and VizAlerts, though, for no real reason.

                          • 10. Re: Email Body in Measures don't work
                            Jonathan Drummey

                            @Martin - I hadn't seen your update before responding, nice work!

                             

                            @Matt - Thanks for the update!

                             

                            Jonathan

                            • 11. Re: Email Body in Measures don't work
                              Martin Ocando

                              Ok, I found more problems when I tried to include the metrics in the mail body, as it turns out that those metrics are controlled by the filter (72 hour of sysdate), and I wanted to measure the whole dataset, so I can get a trend, since the moment it started measuring (11 days go). I could't find a way to exclude a filter, using LOD commands, and I believe it cannot be done, since filters are above LOD, if I remember correctly.

                              So, instead of subscribing to this view, what I did is subscribed to the cross tab called Space Predictions, which doesn't use any filter or dimensions, and I added the 72 hour graph to the email using a VIZ_IMAGE instead.

                              It worked perfectly, I didn't need any excluding, and the metrics show the full dataset, as I need to.

                              • 12. Re: Email Body in Measures don't work
                                Jonathan Drummey

                                Sounds good!

                                 

                                FYI you can get the results you want by using a FIXED LOD (that is processed before dimension filters but after context filters), however as Matt noted using a view that is generating a bunch of marks puts extra load on Tableau Server & VizAlerts. So like where you ended up I generally use cross tabs for Advanced Alert trigger views because a) it's easy to control how many marks they have and b) verify that the calculations are working as desired.

                                 

                                Jonathan