1 2 Previous Next 15 Replies Latest reply on Sep 6, 2013 10:16 AM by Matt Lutton

    constant countd

    Rossella Blatt Vital

      Hi,

       

      I would like to compute the total number of different dates present in my dataset and use that CONSTANT number for further calculations. For example:

       

      DATECLIENTPOS
      8/30/2013A1
      8/23/2013A3
      8/16/2013A5
      8/9/2013A2
      8/2/2013A6
      8/23/2013B4
      8/16/2013B6
      8/2/2013B4
      7/26/2013B10

       

      COUNTD(DATE) = 6

       

      I would like to compute the average POS by dividing the SUM of POS with the total number of different dates, i.e.:

       

      CLIENT A --> (1 + 3 + 5 + 2 + 6)/ 6

      CLIENT B --> (4 + 6 + 4 + 10)/ 6

       

      Instead when I use COUNTD(DATE) in the calculation, only the number of different dates that apply to that specific client will be used, i.e. 5 for CLIENT A and 4 for CLIENT B.

       

      How can I calculate the number of different DATE elements and keep that value constant for all my further computations?

       

      Thanks

      Rossella

        • 1. Re: constant countd
          Matt Lutton

          I'm not sure why this would occur based on your description, but a packaged workbook or screenshot of what you've set up would be helpful in understanding the problem.

           

          Can you show us your exact calculations, if nothing else?

          1 of 1 people found this helpful
          • 2. Re: constant countd
            Rossella Blatt Vital

            Hi Matthew,

            thanks for your reply.

            I have attached an example of the problem.

             

            In the example, you'll see that the sum of POS is divided by the number of different dates for that particular client and not the overall number of different dates (for all clients) :

             

            CLIENT A --> (1 + 3 + 5 + 2 + 6)/ 5 = 3.4

             

            CLIENT B --> (4 + 6 + 4 + 10)/ 4 = 6

             

            This is instead what I would like:

             

            CLIENT A --> (1 + 3 + 5 + 2 + 6)/ 6 = 2.83

             

            CLIENT B --> (4 + 6 + 4 + 10)/ 6 = 4

             

            It sounds to me like a simple problem and probably it's simple solution, but I can't figure out how to do it right. I tried also to use table calculations, but still I am doing something wrong.

             

            Thanks in advance for any help.

            Rossella

            • 3. Re: Re: constant countd
              Matt Lutton

              Here you go.  Because you have Client A & B in the view, Tableau calculates the result based on each client's matching date records.

               

              In order to resolve this I turned your "N Different Days" calculation into a Table Calculation (which also turned the AVG POS calculation into a Table Calculation in your view).  This allows you to compute the results however you want--in this case, I just wrapped your calculation in TOTAL(), and then in the view, I right clicked on the Avg POS calc in your view and chose "Compute Using>>Client" which results in the evaluation you wanted.

               

              Table Calcs are dependent upon what is in your view, so if you remove pills or move things around, the calculation may stop working.  If you need help setting it up further with more stuff in the view, take a screenshot of view's setup (what pills are on rows, columns, and text shelf), and we can help you set it up for a more advanced view.

               

              As always, there may be other ways to accomplish this but learning some basic Table Calculations in Tableau will greatly improve what you are able to accomplish.  Just remember, Tableau evaluates calculations based on the setup of your dimensions, so it makes sense that it was calculating the distinct count of dates per client in this example.

               

              I've learned very basic Table Calculation stuff by reading a lot of the stuff at the link below, over and over and over... and by participating in these forum threads!

              Want to Learn Table Calculations? Here’s How! | Drawing with Numbers

               

              Best of luck!

              1 of 1 people found this helpful
              • 4. Re: constant countd
                Rossella Blatt Vital

                Hi Matthew,

                 

                thanks a lot for your help.

                Your solution is almost exactly what I need: the problem with it is that it will work only when in the view there are ALL clients. Instead, client is a field that the user can select from some filters in this and also other views.

                In the worksheet, if you select only client A, the new value will become 3.4 because we filtered out client B which has the 6th different date:

                 

                (THIS IS CORRECT BECAUSE IT HAS ALL CLIENTS IN THE VIEW):

                 

                (BUT THESE ARE NOT BECAUSE WE ARE FILTERING CLIENT):

                 

                 

                So basically what I am trying to achieve is to use the number of different days as a constant, regardless of the selected fields. Only the sum of POS will change based on the selection of the other fields.

                 

                Many thanks for your help,

                Rossella

                • 5. Re: constant countd
                  Rossella Blatt Vital

                  PS: thanks for the link on table calculations: it is very useful and well written. But as you said, I will probably need to read it and read it and read it again...Table calculations can be so tricky...Also the Tableau wiki seems a useful resource I can learn a lot from! Thanks!

                  • 6. Re: Re: constant countd
                    Matt Lutton

                    This is a common issue, but easily remedied using the steps laid out here:

                    A Jedi (Filter and Table Calc) Trick | Tableau Software

                     

                    See attached for example.  Don't ask me how it works, but it does!

                    • 7. Re: Re: constant countd
                      Rossella Blatt Vital

                      Wow!! That is impressive... I will try to spend some time to understand why that does the trick..

                      In the meantime, thank you very much. I would have never figured it out all by myself!

                      Thanks

                      Rossella

                      • 8. Re: Re: Re: constant countd
                        Rossella Blatt Vital

                        Hi Matthew,

                         

                        thanks again for your help. The constant count now works perfect, but I think it broke something else.

                        I have replicated the problem in the worksheet in attachment. I'll try to explain it, but I think that it will be much more straightforward to just look at the file in attachment.

                        My data contains multiple columns with slightly different client lists, with many common elements (clients) among them. For example, we may have client 1, client 2, measure 1 and measure 2. client 1 and client 2 will have many common elements. When measure 1 is null, measure 2 is not and viceversa. Please note that I oversimplifying the data for the sake of replicating the problem: I have already joint whenever possible.

                        What I need to do is to display a dashboard with a chart using the client filter (necessary for the constant computation of number of different dates) with client 1 as detail; when the user selects a client from this visualization, he will jump to another dashboard that displays measure 2 and client 2. I did this simply creating an action on dashboard 1 that links client filter with client 2. Everything works fine, except that I would like also to display a text when measure 2 is null because the client selected from the first visualization doesn't exist. If I ise client 1 and client 2 only, everything works fine. But if I use client filter, then the text is not displayed at all when that client doesn't exist. Infact, if you look at the data, you'll notice that "my string" is correct for D too... But for some reason it is not displayed correctly in the dashboard:

                         

                        9-6-2013 11-21-30 AM.jpg

                         

                        For example, if you select bubble A, B or C from DASHBOARD VIZ 1, then dashboard VIZ 2 will display correctly both the treemap and the correct text; if you select though bubble D from DASHBOARD VIZ 1, then the text string is empty rather then displaying the text "There are no activity for this client".

                         

                        Any idea why is this happening and how to work around the issue?

                        Thanks a lot for any suggestion.

                        Rossella

                        • 9. Re: Re: Re: Re: constant countd
                          Matt Lutton

                          Yes, this is because you specified a field for the dashboard action.  Changing it to "All Fields" rather than specifying whatever field you had selected fixes this issue.  I rarely specify a field for the action, as Tableau is generally smart enough to do what you want without this specification.  See attached.

                          1 of 1 people found this helpful
                          • 10. Re: Re: Re: Re: Re: constant countd
                            Rossella Blatt Vital

                            Hi Matthew,

                             

                            thanks for your prompt reply.

                            You are right: in the example that I provided removing the specified field for the dashboard action will solve the issue, but in my dashboard with the real data, when I do so, the other visualizations don't update correctly, that is, the client is not passed to the other dashboards. Maybe because I have several fields with common elements (like client 1 and client 2) and multiple dashboards that use these fields and yet need to be updated based on the selection in the other dashboards. So I need to keep the specified field for the dashboard action.

                            Do you have any other idea?

                            One solution I though of is to create a overall client list (like a union of the clients) and to use that to link the fields (see attachment). But if you have a better solution I'd love to hear that.

                             

                            Many thanks

                            Rossella

                            • 11. Re: Re: Re: Re: Re: constant countd
                              Matt Lutton

                              I have no idea, but I'm sure it is something simple.  Make sure the field you are using for the action filter appears in both sheets, even if its not shown in the view (can put it on the Detail shelf and use it).  I'm sorry, but without seeing your dashboard, it is difficult for me to help.

                              1 of 1 people found this helpful
                              • 12. Re: Re: Re: Re: Re: constant countd
                                Rossella Blatt Vital

                                Hi Matthew,

                                 

                                I am trying to replicate the problem but so far I wasn't able to and unfortunately I can't share my data. But your help is very useful anyway. Thank you very much.

                                One of the field used in the action can appear only as filter in the view because I am aggregating at a higher level (exchange) and if I drop client on the detail each rectangle will be splitted into multiple rectangles (see image). Instead I'd like to have only one rectangle for color.

                                 

                                9-6-2013 11-58-38 AM.jpg

                                 

                                I will post the solution if I find the cause of the issue or I will try to replicate the problem with the fake data.

                                 

                                Thank you.

                                Rossella

                                • 13. Re: Re: Re: Re: Re: constant countd
                                  Matt Lutton

                                  I know this problem well.  You should be able to remove the borders so its all one solid color.

                                   

                                  Border.png

                                  The individual boxes will still exist, but won't be visible.  However, if you have a filter/action from there, the individual boxes will correspond to the individual client, and not the whole area.  Hopefully that makes sense.

                                  1 of 1 people found this helpful
                                  • 14. Re: Re: Re: Re: Re: constant countd
                                    Rossella Blatt Vital

                                    Hi Matthew,

                                     

                                    thanks: that's a nice trick!

                                    Unfortunately I do need to use the rectangles as filters to update another dashboard and I need the new dashboard to display the overall values, not only for that specific client. Maybe I can figure out a workaround to this.

                                     

                                    Thanks

                                    Rossella

                                    1 2 Previous Next