-
1. Re: Need field to have conditional value if "All" is selected in filter
ShivaRam Chennapragada Apr 9, 2018 8:26 AM (in response to Scott Kailey)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 Apr 9, 2018 9:54 AM (in response to ShivaRam Chennapragada)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 Apr 9, 2018 10:45 AM (in response to ShivaRam Chennapragada)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 Apr 9, 2018 11:00 AM (in response to Scott Kailey)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 May 9, 2018 11:51 AM (in response to ShivaRam Chennapragada)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 May 10, 2018 7:46 AM (in response to 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 May 10, 2018 8:01 AM (in response to Scott Kailey)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 May 10, 2018 8:21 AM (in response to ShivaRam Chennapragada)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
-
ExampleWorkbook.twbx 19.5 KB
-
-
9. Re: Need field to have conditional value if "All" is selected in filter
Joe OppeltMay 10, 2018 8:29 AM (in response to Scott Kailey)
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 OppeltMay 10, 2018 8:34 AM (in response to Joe Oppelt)
I did this on your sheet. See values in the title of the sheet. See attached.
-
ExampleWorkbook A.twbx 16.3 KB
-
-
11. Re: Need field to have conditional value if "All" is selected in filter
Scott Kailey May 10, 2018 9:02 AM (in response to Joe Oppelt)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 OppeltMay 10, 2018 9:20 AM (in response to Scott Kailey)
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.
-
ExampleWorkbook B.twbx 23.3 KB
-
-
13. Re: Need field to have conditional value if "All" is selected in filter
Scott Kailey May 10, 2018 9:48 AM (in response to Joe Oppelt)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 OppeltMay 10, 2018 9:50 AM (in response to 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.
-
ExampleWorkbook C.twbx 40.5 KB
-