1 2 Previous Next 18 Replies Latest reply on Oct 22, 2018 3:18 AM by Simon Runc

    How to add single field into multiple groups?

    Angela.Koh

      Hi All...

       

      Ive been trying all ways to move a single field to multiple groups but was unable to....

      Below is one of my attempt to create a measure that does it... But it seems that it is only working for the first instance...

      Can anyone help pls? Basically every Queue has to include a 'DEFAULT' queue...

        • 2. Re: How to add single field into multiple groups?
          Simon Runc

          hi Angela,

           

          So I think I'll need to see some data to give you an exact answer, but I can tell you why this is doing this

           

          With an IF statement (or CASE/IIF) once a True has been reached the formula exits at that point, and as every one has an OR = 'DEFAULT' the first one it true, even if [Queue] doesn't equal 'Default (Q110 & Q130)' as it's an OR. So only one side needs to be true for the entire row to be true. In short any time the [Queue] = 'DEFAULT' it will be put in the 1 bucket.

           

          or to put in another way....If a [Queue] ='DEFAULT' then how does Tableau know that some 'DEFAULTS' need to go in 1, some in 2, some in 3?

          1 of 1 people found this helpful
          • 3. Re: How to add single field into multiple groups?
            Chris McClellan

            The way IF works (in any language) is that it's the first condition that is true.

             

            It sounds like you want multiple values returned and it just won't work that way.

             

            Another question is - if the Queue value is DEFAULT what value do you want returned ?

            • 4. Re: How to add single field into multiple groups?
              Angela.Koh

              I have attached a sample workbook which I am working on...

              Because the TSF Target is a forecast calculation which includes 'null' queues, that is why I needed to group it into every individual queue...

               

              The 'null' values alias is named as 'Check for Forecast' here.

               

              As you can see, I will need to tick the 'Check for Forecast' value in order to generate the TSF Target calculation.

              However, when I click on the pie chart to filter a single queue (UU_OTH), the 'Check for Forecast' will be unchecked and it does not generate my TSF Target anymore.

               

               

              So the key point here is that I am unable to put this 'Null' value into all groups... Are there any workarounds for this?

               

              One method is to create a parameter with each field to tag with the null value, but I won't be able to do multiple selections.

               

               

              Simon Runc  Chris McClellan

               

              Hope you guys can advise Thank you!

              • 5. Re: How to add single field into multiple groups?
                Chris McClellan

                As far as I know it's impossible to put a value (even NULL) into multiple groups.

                Angela.Koh wrote:

                 

                 

                So the key point here is that I am unable to put this 'Null' value into all groups... Are there any workarounds for this?

                 

                • 6. Re: How to add single field into multiple groups?
                  Simon Runc

                  Yes I'm with Chris, there isn't a way I know.

                   

                  If you think about a simple example, say Superstore regions, a city can't be in 2 regions? unless you duplicate the data and put one row in one group and the duplicate row in another.

                   

                  I'm out today, but I'll have a look at your workbook tomorrow as we might be able to get something working with LoDs (not the multiple allocation of rows, but a different approach to the problem)

                  • 7. Re: How to add single field into multiple groups?
                    Angela.Koh

                    Simon Runc  Chris McClellan yeah i understand it can't be done normally, but u guys aren't normal and i believe there are workarounds to this.

                    In another example, if I had 10 stalls, 4 stalls sells APPLES and 3 stalls sells PEARS, in such instance, i'd have to put APPLES and PEARS into multiple groups right?

                     

                    This problem is tricky because the 'TSF Target' calculation requires the 'NULL' values hence i am trying to group the 'NULL' into every group.

                    Looking forward to see if anyone can solve this!

                    • 8. Re: How to add single field into multiple groups?
                      Simon Runc

                      ....but u guys aren't normal

                      Flattery will get you everywhere!! ...However even with our special ability we can't change the fundamentals of how a database works, and store 2 values in a row! In your store example the data would look like

                       

                       

                      However I do think there is a workaround, but just not by having the same level represented in 2 different ways in the same dimension.

                       

                      I've had a quick look, and will take me too long to decipher how the [[forecast] TSF required] field is calculated. If you can give me a description of what that field does, and  how it is calculated (with reference to the other fields it uses, and the level at which it is calculated) I think we can use a FIXED LoD so that it computes correctly, even when NULL is deselected in the filter. No promises!

                      • 9. Re: How to add single field into multiple groups?
                        Chris McClellan

                        Angela.Koh wrote:

                         

                        In another example, if I had 10 stalls, 4 stalls sells APPLES and 3 stalls sells PEARS, in such instance, i'd have to put APPLES and PEARS into multiple groups right?

                         

                         



                         

                         

                        No, not the way GROUPS work - because GROUPS are (sort of) like a 2-level hierarchy.  In your example you could use SETs to show you stalls that sell apples vs pears, or there would be another dimension to show that fruits are being sold.

                         

                        But using GROUPs in Tableau, you must choose 1 value only.  Please investigate SETs or using other dimensions, it might help you as well

                        • 10. Re: How to add single field into multiple groups?
                          Angela.Koh

                          Below is the table to illustrate the formula of TSF Target.

                          October 8 and onwards are 'NULL' because they are FORECASTED values.

                          Hence, when I select any 'Queue', it does not include 'NULL', which is why i am trying to group the 'NULL' into every 'Queue'.

                           

                           

                          Thank you for your time to look into this

                          Simon Runc  Chris McClellan 

                          • 11. Re: How to add single field into multiple groups?
                            Angela.Koh

                            Chris McClellan if I can create a dimension that groups my sets properly then it may fulfill the goal.

                            Unfortunately I don't think grouping of sets is possible.

                            • 12. Re: How to add single field into multiple groups?
                              Simon Runc

                              hi Angela,

                               

                              So I think I've managed to unpick everything and getting the formula to compute correct result when "Check for forecast" is unticked. I think there is a lot of extra complexity that could be handled differently if you were going to use this approach from scratch, but I've just adapted the formulas you have. The basic trick is to use FIXED LoDs to get stuff in the future rows of data into the current rows of data (under the bonnet it's a bit complicated, but that's the basic idea)

                               

                              The new formula is

                               

                              [[forecast] TSF required_SR]

                              ((SUM({SUM({FIXED [Date only]: SUM(IIF([QG driller]='Check for Forecast',

                              [[forecast]] Number of calls needed to be accepted (F+P)],null))})})

                              +

                              SUM([[forecast]] Number of calls needed to be accepted (F+P)]))

                              -

                              SUM([[forecast]] Number of calls accepted in threshold (P)]))

                              /

                              SUM({SUM({FIXED [Date only]: SUM(IIF([QG driller]='Check for Forecast',

                              [[forecast]] Number of calls offered (F)],null))})})

                               

                              It only works when 'Check for Forecast' is unticked (when it's ticked there is some double counting, so basically doubles the value). I've added my new calculation to your KPI tile, so you can test. You can select some QR Driller and see the result you got, and then untick the "check for forecast" option and check my version returns the same result.

                               

                              hope that helps...and myself and Chris retain our "not normal" status

                              • 13. Re: How to add single field into multiple groups?
                                Angela.Koh

                                hi Simon Runc

                                Thank you for your time to look into this. I hope you had a great weekend.

                                This worked almost perfectly! U guys r truly the gurus of tableau...

                                Just 1 small problem, the 'QG driller' has to be added to context for the calculation to be accurate.

                                However, when I tick add to context, the TSF target does not appear

                                • 14. Re: How to add single field into multiple groups?
                                  Simon Runc

                                  Ah...didn’t realise that had to be a context filter (I checked a few

                                  different QG driller entries & seemed to get the same value you had with

                                  the null/checked for forecast method...must have just been good luck). My

                                  method won’t work with a context filter, as it takes advantage of the way

                                  LoDs are computed before any regular filters are applied. It is this that

                                  allows us to access values in the null/checked for forecast, even when it’s

                                  filtered out. A context filter has the effect of bumping the filter up the

                                  calculation pipeline, so the filter is applied before any regular filters

                                  are applied...so once we make the filter context the null/checked for

                                  forecast values are filtered out as before.

                                   

                                  If it has to be a context filter to make it work, the only thing I can

                                  think is to duplicate the data rows, so each QG Driller can contain both

                                  the null/checked for forecast and its actual values.

                                   

                                  I’m away this week but I’ll take a look when I’m back. What is the

                                  datasouce for your real world version? This will help me in thinking the

                                  best method to duplicate the data rows (eg if it’s Excel we can union it

                                  with itself, but if it’s a SQL database then we might need to use custom

                                  SQL)

                                  On Mon, 15 Oct 2018 at 04:20, Angela.Koh <tableaucommunity@tableau.com>

                                  1 2 Previous Next