1 2 3 Previous Next 31 Replies Latest reply on May 14, 2018 1:04 PM by Scott Kailey

    Need field to have conditional value if "All" is selected in filter

    Scott Kailey

      Hello,

       

      I have been searching for a solution to my problem and nothing I am finding seems to fit exactly what I need.  I am hoping someone can help me.

       

      I have a report that I do that summarizes a bunch of data based on a particular "deal name".  If the filter is selected it sums up the info for that deal.  What I need to happen is to sum up the data for all deals if "All" is selected in the filter but instead of listing all of the deal names on the report, I need it to be called "All deals" or something like that so it doesn't show everything in the report. I am assuming I would then base the filter off of a new calculated field that does that but I am not sure.  I hope this makes sense.

       

      Thanks!

        • 1. Re: Need field to have conditional value if "All" is selected in filter
          ShivaRam Chennapragada

          Hi Scott,

           

          I had a similar requirement, so this is what I did and hope this helps you answer your question.

           

          I have a Dimension = Account Sub Type under which I have an attribute 'Fuel Type' under which at Account Group Level this is broken down to 'Gas' and 'Diesel', I would like to calculate gallons sold at each level, so I created a parameter like this (I added All for my aggregation), In your case it would all the individual deals. For this do 'Add from Field' in addition to existing fields add 'All'

          Then created a Calculated field like this, I have this on filter and set to 'True'

           

           

          I use the parameter to control what I'd like to see, I also have measures that give me the actual numbers.

           

          Thanks,

          Shiva.

          • 2. Re: Need field to have conditional value if "All" is selected in filter
            Scott Kailey

            Thanks Shiva!  I will give this a try as soon as I get a chance and report back.  I appreciate it.

            • 3. Re: Need field to have conditional value if "All" is selected in filter
              Scott Kailey

              Shiva, I beleive I have the parmameter set up correctly, however, I am not sure how to add it to my worksheet or dashboard to work with my filter.  If I am to use it instead of the filter for my deal names, how do I need to go about that?

              • 4. Re: Need field to have conditional value if "All" is selected in filter
                ShivaRam Chennapragada

                Scott, you'll have to trigger the Parameter by calling it in the calculated field and add this calculated field to Filter. Then right click on the parameter and select 'Show Parameter Control'. So, if you look at screenshots in my previous reply- you'll notice how I called 'Gallons Sold Parameter' in the calculated field. Hope that helps.

                • 5. Re: Need field to have conditional value if "All" is selected in filter
                  Scott Kailey

                  Hi Shivaram.  I am still having trouble with this and hoping you could further help.  I want to make sure I am clear on what I need.

                   

                  I have a datasource with a field named deal name.  In that field, there is a possibility of about 20 values.  About 5 of those values belong in Group 1 and the rest belong in Group 2.  My main filter for all worksheets across the workbook is based off of the deal name field.  I am trying to summarize everything in one group or the other when "All" is selected.  For example, If I want to summarize all 5 values across the workbook for Group 1, I want to do that and basically rename the deal name as "Aggregate".  Same with Group 2.  However, I still want to be able to use my filter to give me the totals of the individual deals across the workbook.

                   

                  I just can't seem to figure this out.

                  • 6. Re: Need field to have conditional value if "All" is selected in filter
                    Scott Kailey

                    Is there anyone else that can provide some input on this problem?

                    • 7. Re: Need field to have conditional value if "All" is selected in filter
                      ShivaRam Chennapragada

                      Scott- Could you mock up some data and attach a sample workbook?

                      • 8. Re: Need field to have conditional value if "All" is selected in filter
                        Scott Kailey

                        Hi ShivaRam,

                         

                        I have attached a workbook that simulates my problem.  You will notice a columns in the excel worksheet for Deal Name, Balance, and Channel.  A couple of things I am trying to do:

                         

                        1.  I need two seperate worksheets to pull into dashboards.  One that would include some sort of a filter for the Channel B and one for the Channel F, each having multple deal names tied to them.

                        2.  If All is selected in the filter on the worksheet for either of the Channels, I need it to summarize everything for that channel into one "Deal Name" called "Aggregate".

                        3.  Whichever filter is chosen, I need it to apply to all other worksheets using the same data.

                         

                        I hope this makes sense.  Please let me know if you have any other questions after taking a look and thank you for your help with this.

                         

                        Scott

                        • 9. Re: Need field to have conditional value if "All" is selected in filter
                          Joe Oppelt

                          Scott -- There is no "ALL" function (or even an "All" value) for filters.  You have to create some calcs to know what's happening on the sheet.

                           

                          { FIXED : COUNTD([Deal]) }

                           

                          This will tell you how many Deals are in your database.

                           

                          { EXCLUDE [dimension 1](, [dimension2], ... [dimension last one on sheet]) : COUNTD([Deal]) }

                           

                          This one will tell you how many are in the underlying table for that sheet (which essentially tells you how many were selected by the filter.)  Put all the dimensions that are on the sheet in that exclude list.

                           

                          If calc 1 = calc 2, then the user selected All.

                          • 10. Re: Need field to have conditional value if "All" is selected in filter
                            Joe Oppelt

                            I did this on your sheet.  See values in the title of the sheet.  See attached.

                            • 11. Re: Need field to have conditional value if "All" is selected in filter
                              Scott Kailey

                              Thanks Joe.  That seems to work similar to when I simply select All in the filter.  How would I summarize the totals to call it "Aggregate" for the deal name?  When I do this, how do I get it to apply to all worksheets with the datasource (I don't have any other worksheets in this example book, but in mind I have over 20)?

                              • 12. Re: Need field to have conditional value if "All" is selected in filter
                                Joe Oppelt

                                I'm going to answer this in two steps.

                                 

                                The first is to show you sheet swapping.

                                 

                                Usually when you want to show things one way for ALL and another way for "some", make two sheets, and swap them based on the condition.


                                I made a copy of your original sheet.  I took off [Deal Name] from Rows, and inserted "Aggregate" in there.  Since [Deal Name] is not partitioned on the sheet now, the value is just the sum of all deals.


                                then I made a calc to compare the two numbers I previously created.  If they are equal, then calc = 1 else calc = 0.  (See [Display Aggregate?].)  I put this as a filter on both sheets, and for one sheet I select value = 1 (thus, ALL is selected), and for the other I selected value = 0 (meaning less than ALL is selected.)  Then I put both sheets on Dashboard 1.  Oh, and I did "Apply to" for the [Deal Name] filter so that it controls both sheets.  Play with the filter on Dashboard 1.  You'll see how this works.

                                 

                                GO to Dashboard 2.  Here I made a floating container and dragged both the sheets into that container.  Since only one displays at a time, they "swap" in place within the container.


                                That's sheet swapping.

                                 

                                See attached.

                                1 of 1 people found this helpful
                                • 13. Re: Need field to have conditional value if "All" is selected in filter
                                  Scott Kailey

                                  Thanks Joe.... I will take a look at this in an hour or so and report back.  I really appreciate you taking the time to build this.

                                  • 14. Re: Need field to have conditional value if "All" is selected in filter
                                    Joe Oppelt

                                    If you have 20 sheets, then sheet swapping would involve 40 sheets, and maybe you don't want to maintain that.

                                     

                                    In the attached, see Sheet 1(3).  Here I made a calc called [Deal].  If we have ALL, then the value of [Deal] is "Aggregate".  For all rows.  Otherwise propagate the [Deal Name] into [Deal].  I put that on ROWS instead of [Deal Name].  Take a look at how [Deal] works on sheet (3).  (Don't worry about the multiple values yet when it's "Aggregate".)  I also created a new Balance calc to sum up all the Deal values when it's aggregate.

                                     

                                    You'll notice that on Sheet (3) the window sum is just propagating the values from the individual deals.  Go to Sheet (4).  Here I change the way the table calc works so that it addresses along the [Deal Name] dimension.  Note:  I have to have [Deal Name] on the details shelf so that the dimensionality of that field is maintained on the sheet.  The [Deal] calc ends up as a measure because the [Deals on sheet] calc has to be a measure, and it's part of what makes [Deals].  And the presence of [Deal Name] on the sheet forces 5 values into the "aggregate" cell.  (It's just how Tableau works.)

                                     

                                    Anyway, I made the table calc [Swap balances] to run along [Deal Name]. and now we get the proper value for the balance.  (But we still have 5 instances of the value.)

                                     

                                    Go to Sheet (5).  Leave all 5 [Deal names] selected when you go to sheet 5.

                                     

                                    Here I added another calc, [index] to the sheet on the filters shelf.  I edited that table calc to run along [Deal Names]. and then I edited the filter to select only for value = 1.  Now we get only the first instance of the 5 that were displaying for "aggregate'.  But there is a catch here.  If we select one less [Deal Name], that same filter only displays the first [Deal]!  SO I addressed that in sheet 6.

                                     

                                    In (6) I dragged [index] to the detail shelf and added one more calc to control whether I want to allow only one value or all values of index.  Take a look at [Display 1 Aggregate].

                                     

                                    Now the sheet behaves the way you want.  It takes a bunch of table calcs to control this (which is why it's easier to sheet swap), but now you don't need double the sheets.

                                    1 of 1 people found this helpful
                                    1 2 3 Previous Next