10 Replies Latest reply on Jul 19, 2018 10:17 AM by Michel Caissie

    Display Count of Marks as a BAN

    Ben Peterson

      The attached workbook is based on a dataset that lists members. Members may have zero, one, or two "training indicators" (a "Y" or null value). That means each member may a member of the "Level One" set, the "Level Two" set, both, or neither. The "Level One" set has 3279 members (marks), while the "Level Two" set has 1734 members (marks). 1423 members belong to both sets. What I want to do is display the number of marks - the same value shown in the lower left of the Tableau window - as a Big *** Number. I'm sure that I am missing something simple...I've tried Size(), Window_Count, etc.

      tableau forum.png

        • 1. Re: Display Count of Marks as a BAN
          benjamin pasmore

          Try putting the dimension "training indicator names" into the columns shelf. 

          • 2. Re: Display Count of Marks as a BAN
            Michel Caissie

            Ben ,

             

            If you look on Sheet 16(2),

            I first move the two MemberID dimensions on the Rows.

            Then I do  a first calculation1

            if [Training Indicator Names Intersection Filter] then [# Pivot Field Names]  end

             

            Next, for calculation2  I Window_Sum this  last number

            WINDOW_SUM( [Calculation1] )

             

            Next I get the number of Names selection

            TOTAL( COUNTD( [Training Indicator Names] ) )

             

            and I get the final number with

            if [Nb of TIN selections] = 2 then

            [Calculation2] / 2

            else

            [Calculation2]

            end

             

            pay attention to the computing...

             

            In Sheet 16(3)  I get a single value,  but it is tricky.

            First I hide the Dimension headers,  and next a create a filter

            last() = 0

            to keep a single row.

             

            It works in your example, but it is because the row corresponding to last()=0  is part of the intersection.

            So it could stop working on a refresh of the data. But I dont see a better solution, other than removing the filter and adding a mask in a dashboard to show a single value.

             

            Michel

            • 3. Re: Display Count of Marks as a BAN
              Ben Peterson

              Thank you so much Michel!! Surprised that it took so many calculated fields to show what Tableau already shows on the lower left of the screen!

              The last thing you wrote...

              last() = 0 gives us the last row of the view, right? You said it might not always work, could you please elaborate? I already filter the Training Indicator Values to exclude nulls. Would whether it works depend on how the view is sorted?

               

              Ben

              • 4. Re: Display Count of Marks as a BAN
                Ben Peterson

                It seems Calculation3 could be rewritten to accommodate more Training Indicatior Names if they are added

                if [Nb of TIN selections] = 1 then

                [Calculation2]

                else [Calculation2] / [# Pivot Field Names Selected]

                end

                • 5. Re: Display Count of Marks as a BAN
                  Michel Caissie

                  If you create a calculation  last()   (table down)  and put it on  the Text shelf on Sheet16(2), you will see that  some values are missing. Starting from the button , you have  0, 1 then it skip to 12 then 16 etc,...

                   

                  This is because you have a table calculation on the Filters shelf, which impacts the order of operations. All table calculations are then computed before the other filtering is applied. This means that the  last() functions is computed on all rows, including those filtered out.

                   

                  So in your case, rows with index  2,3,4 etc,... (starting from the end)  are filtered out,  because the MemberIds of those rows are not part of the intersection.

                   

                  This means that if you have a scheduled refresh of the datasource and you add data to your dataset where the memberId  corresponding to the last row is not part of the intersection, nothing would display.  You would then have to manually replace the last() = 0  for  last() = x   , where X is a valid value returned by last()  that is not filtered out by the   Intersection filter.

                   

                  Or if you know that a MemberId  will always be part of your dataset and will always be part of the intersection, you could use a filter like

                  LOOKUP( MIN([MemberID]),0 ) = 'MBR1B1B96B5'

                  • 6. Re: Display Count of Marks as a BAN
                    Ben Peterson

                    Thanks for the explanation. Would you have used some other way to find intersections? A way that would avoid these issues? I've heard that some people perform a cross-join on the data before it gets into Tableau. etc.,

                    • 7. Re: Display Count of Marks as a BAN
                      Michel Caissie

                      Here is an alternate solution without using any table calculations.

                       

                      I used lod and parameters.

                      With only two Indicator names it would be quite easy.

                       

                      First I create a boolean for each Indicator name

                      {FIXED [MemberID]: MAX( if [Training Indicator Names] = "Level One Training Indicator" and [Training Indicator Values] = "Y" then 1 else 0 end )} = 1

                      So if the Name is Level One and Values Y , return 1, and if the MAX per MemberId is 1 then return true.

                       

                      Next I create a parameter for the selection,  and the following filter

                      case [Select Indicator Name]

                      when 'Both' then if [isMemberLevelOne] and [isMemberLevelTwo] then 1 else 0 end

                      when 'Level One' then if [isMemberLevelOne] then 1 else 0 end

                      when 'Level Two' then if [isMemberLevelTwo] then 1 else 0 end

                      else 0

                      end

                       

                      I bring this filter on the filter shelf and keep the  1s,  and the only thing I need is  the COUNTD(MemberId)  on the text shelf.

                      see Sheet 18

                       

                      If you have more than two Indicator Names  and want to be able to select any combinations, then you need one parameter per Indicator Name.

                      (if you need to select only one a a time, you can use a single parameter with all values and add then to the first solution).

                      The filter would look like this

                      if [Select Level One ?] = 'Yes' and [Select Level Two ?] = 'Yes' then

                          [filter (Level One)] and [filter (Level Two)]

                      else

                          [filter (Level One)] or [filter (Level Two)]

                      end

                       

                      This filter would manage all the logic if you have more than two Names.

                      see Sheet 18(2)

                      • 8. Re: Display Count of Marks as a BAN
                        Ben Peterson

                        Michel, thanks for the alternate concept. Actually, though, there could be up to 8 training indicator names. If I understand this new method, the filter would have to consider 8! (40,320) options to account of all the combinations of 8 training indicator names!

                         

                        Another thing I've wondered about, is whether it is possible to show a count of filtered Member IDs by another dimension, Imagine a Group indicator is added to the data set. The filter that determines whether a Member ID is eligible for the Training Indicator(s) selected operates at the level of detail of Member ID, thus Member ID is on the detail shelf. Obviously it is possible to do something like this, where every one of the marks is visible:Untitled.png

                        Is it possible to do something like a bar chart, or a donut chart, in which the colored areas are not segmented into every mark. Do you know if it is possible to filter at the level of Member ID, while at the same time aggregate a count of Member IDs at the level of Group ID?

                         

                        I realize this might be considered a separate question on the forums. Let me know if you think I need to create a new thread. Thank you so much for what you have taught me so far!

                         

                        Ben

                        • 9. Re: Display Count of Marks as a BAN
                          Ben Peterson

                          Michel, I've been able to get around the last() = 0 inconsistency by sticking Calculation3 in detail and in the sheet title. Then I hide everything else on the sheet.

                          Thanks.

                          • 10. Re: Display Count of Marks as a BAN
                            Michel Caissie

                            Ben,

                             

                            If it can help, in the attached I have another alternative  (Sheet 20 (3)).

                             

                            To get the intersection,  I compare the count of names for a user  with the count of names  selected

                             

                            isMemberHaveAllNames =

                            [Indicator Names Selection Count] = [MemberID Names count]

                            pay attention to the computing, you have a nested calculation here with different computing for each.

                             

                            where

                            [Indicator Names Selection Count]  is

                            TOTAL( COUNTD( [Training Indicator Names] ))

                             

                            and

                            [MemberID Names count] is

                            WINDOW_MAX( index() )

                             

                            I keep a single member values with (to replace the last() = 0)

                            ATTR( [MemberID]) = WINDOW_MIN(  if [isMemberIdHaveAllNames] then ATTR( [MemberID] ) end)

                             

                            and a single row for that member with

                            first() = 0

                             

                            again, pay attention to all the computings it is very error prone.

                             

                            As you can see, it takes the groupId into  account , so you can do a bar chart.

                             

                            I though that I could filter on MemberId, by adding the MemberId filter into context (to make sure the MemberId is filtered before the computing occurs),

                            but for an unknown reason, it doesn't work properly.

                             

                            Anyway, here is the workbook, maybe you will find useful hints in it.