13 Replies Latest reply on Jun 20, 2013 11:15 AM by Amy Song

    Limit to a subset

    Amy Song

      Hi there, I have two questions I'm hoping to get insight into:

       

      Question 1

      I have a view that shows vehicles by model year for a selectable date range.  I want to be able to limit this view by vehicles of a certain transaction frequency (i.e, "Show all vehicles", "Vehicles moving 3+ times a day", "Vehicles moving between 1-3 times a day", and "Vehicles moving up to once a day" on average).  I created a calculated field that calculates average moves per day, and another that categorizes them into the above groups.  Lastly I created a parameter to allow the user to control to these groups.  I thought I could just pull the vehicle id field (RFID number) into filters and use a conditional filter for [move groups]=[move parameter], however I'm getting an error saying [move groups] is invalid to use?  How can I filter my set to the group of vehicles I want?

       

      Question 2

      Depending on the order in which I pull in my RFID number (vehicle id) and move groups, I'm getting nulls.  If I pull in RFID number first, then move groups, everything is fine (2nd tab).  If I pull in move groups first, then RFID number, I have a null group with no avg move per day metric, even though there should be (3rd tab).  Can someone tell me why?

       

      Thank you!!

        • 1. Re: Limit to a subset
          Shawn Wallwork

          Amy, it looks like you're working in V7 so I can't post a workbook for you, but here are the answers:

           

          Q1 - Instead of filtering on RFID, created a calculated field with this code in it:

           

               IF [move groups]=[Move parameter] OR [Move parameter]="1" THEN TRUE ELSE FALSE END

           

          Put this field on the filter shelf and select TRUE. That should do it.

           

          Q2 - Change the Move Groups table calculation to Compute Using RFID Number. This should get rid of the NULLs.

           

          Hope this helps,

           

          --Shawn

           

          PS: I'm not sure your Move Frequency is setup correctly, but that might be something I messed. Just let me know if you need help with it.

          • 2. Re: Limit to a subset
            Amy Song

            Hi Shawn,

            We just upgraded to Tableau 8 and here is another attached example workbook.

             

            Made some updates since then.  What I'm looking for though is still how to limit the subset for a rolled up view.  If you look at the attached workbook, the first tab "Details" behaves exactly how I want it to; i.e. will show me all vehicles that had a certain move frequency.  But how do I get the total count for a frequency group?  The calculation filter does not work in the "Overview" tab because the data is not at vehicle level granularity.

             

            Another idea I thought of is using the details tab as a dashboard action, adjacent the Overview view.  But that seems kind of cumbersome to filter the details, and drag and highlight down the list of RFID numbers (distinct vehicles).  But that is the essential mechanism I'm looking for.

             

            Thanks,

            Amy

            • 3. Re: Limit to a subset
              Shawn Wallwork

              twbx

              • 4. Re: Limit to a subset
                Shawn Wallwork

                [Apologies if my last post was a tad bit too terse.]

                 

                Amy, I'm sure we can work this out but you'll need to post a packaged workbook (twbx). The one you posted (twb) doesn't include any data (as I suspect you already know).

                 

                Cheers,

                 

                --Shawn

                • 5. Re: Re: Limit to a subset
                  Amy Song

                  No worries -- my mistake.  Here is the packaged workbook.  Thanks for any help.

                  • 6. Re: Limit to a subset
                    Shawn Wallwork

                    Amy, I'm not having much luck with this one so I'm pinging Joshua Milligan. He seems to be active this afternoon.

                     

                    --Shawn

                    • 7. Re: Limit to a subset
                      Amy Song

                      Thanks for looking into it.  Josh (or whoever else might take a look), I'm also interested in adding the total number of vehicles in the title of the 'details' tab view.  countd(RFID Number) isn't working though since it's just giving me 1 for each row.  I want it to say a total of 2,354 for All, 209 for Greater than 4 days, 566 for Up to four days per week (these are the total rows if you view summary data)... etc...

                       

                      And lastly, is there a way to sort the "freq word" filter?  I know for a dimension you just right click to default properties and manual sort, but doesn't look like there's an option for that as a measure.

                      Thanks!

                      • 8. Re: Limit to a subset
                        Joshua Milligan

                        Amy,

                         

                        I'm looking into it!

                         

                        --Joshua

                        • 9. Re: Re: Re: Limit to a subset
                          Joshua Milligan

                          Amy,

                           

                          Here is an attempt at a solution.  My first thought was to use table calculations, but I believe everything can be done using row-level and aggregate calculations.  If anything looks off, please let me know.

                           

                          The workbook has some inline documentation, but I'd be happy to answer any questions you might have!

                           

                          Regards,

                          Joshua

                          • 10. Re: Limit to a subset
                            Amy Song

                            Hey Josh,

                            Thanks for your help!  I tried to make a couple edits and coming up with errors.  First, the metric of frequency is actually defined by "move days" as the numerator (a move day is where 1 or more moves occured on the day, so it doesn't matter how many times a truck moved, if it moved at least once, then 1 "move day" is counted), and total number of days in the period as the demoninator.  So here are the changes I made:

                             

                            • Numerator # Moves - I changed to "# Move Days", defined as COUNTD(STR([RFID Number]) + STR(DATETRUNC('day',[ReadDateTime])))
                            • Denominator # Days - I changed to "# Days in period", defined as datediff('day',WINDOW_MIN(min([ReadDateTime])),WINDOW_Max(max([ReadDateTime])))

                             

                            Also, the buckets I need are a bit different (need 7 total, mutually exclusive) so I changed the Move Frequency Parameter to 0-6 and bucket names.  I also updated the Move Frequency Filter to this calculation:

                             

                            IF [Move Frequency Parameter] = 0 THEN 1

                            ELSEIF [Move Frequency Parameter] = 1 AND [Avg Moves Per Day ]<=(1/30) THEN 1

                            ELSEIF [Move Frequency Parameter] = 2 AND [Avg Moves Per Day ]>(1/30) and [Avg Moves Per Day ]<=(1/7) THEN 1

                            ELSEIF [Move Frequency Parameter] = 3 AND [Avg Moves Per Day ]>(1/7) and [Avg Moves Per Day ]<=(2/7) THEN 1

                            ELSEIF [Move Frequency Parameter] = 4 AND [Avg Moves Per Day ]>(2/7) and [Avg Moves Per Day ]<=(3/7) THEN 1

                            ELSEIF [Move Frequency Parameter] = 5 AND [Avg Moves Per Day ]>(3/7) and [Avg Moves Per Day ]<=(4/7) THEN 1

                            ELSEIF [Move Frequency Parameter] = 6 AND [Avg Moves Per Day ]>(4/7) THEN 1

                            ELSE 0

                            END

                             

                            Anyway, with these changes made, the RFID Number conditional filter failed; the error tells me that the Move Frequency Filter is invalid.  Can you explain why?  Edited workbook attached.

                             

                            Thanks!

                            Amy

                            • 11. Re: Limit to a subset
                              Amy Song

                              Hey Josh/Shawn,

                              Not sure if you saw my last reply but could you or someone else help answer?  Thanks!

                              • 12. Re: Limit to a subset
                                Joshua Milligan

                                Amy,

                                 

                                Sorry -- somehow I didn't get a notification of your previous post.  I'll try to take a look tomorrow.  Feel free to ping me again if you don't see anything form me by the end of the day.

                                 

                                Regards,

                                Joshua

                                • 13. Re: Limit to a subset
                                  Amy Song

                                  Hey Josh, any luck?