12 Replies Latest reply on Mar 14, 2016 11:29 AM by Niranjan Banarjee

    How to get Minimum or Maximum or Average?

    Niranjan Banarjee

      Hi,

       

      Can anyone help me in getting the MInimum/Maximum/Average from the below table by Rows and with the Min,Max & Avg basically i want to build a line chart with 3 lines.

       

        • 1. Re: How to get Minimum or Maximum or Average?
          Carl Slifer

          Howdy Niranjan,

           

          I've created a workbook because one was not provided.  What I've done is instead of time of the day I've used day of the week to substitute. I've graphed a line so that I have a datapoint for every month and then took the average, min, and max with respect to my days of the week.

           

          Look at the crosstab. I've created several calculations that use WINDOW() type calculations and then I use the scope of the entire table but look down the calculation. In this case I am returning the avg, min, or max for each day in each month, I then created a shared axis graph but in order to have only 1 line instead of one for each day I created a filter which is LAST = () and this too looks down in terms of table calcs. What this means is I only keep the last item in my partition, in my case Saturday. And because for my entire table of days I have the same value that's the avg, min and max I could keep the first, the last, etc and it wouldn't matter.

           

          Let me know if this works for you.

           

          Cheers,

          Carl Slifer

          InterWorks

          • 2. Re: How to get Minimum or Maximum or Average?
            Niranjan Banarjee

            HI Carl,

             

            Can you please resend  your workbook as I am unable to open it.I am using Tableau 9.0

            • 3. Re: How to get Minimum or Maximum or Average?
              Carl Slifer

              Hi Niranjan,

               

              Hopefully you tried to follow the example before looking at the book. I feel it makes us easier to understand where we get and figure out what we do not know.

               

              Cheers!

              • 4. Re: How to get Minimum or Maximum or Average?
                Niranjan Banarjee

                Sorry Carl,

                 

                Still Unable to open your file.It is showing as corrupt.

                • 5. Re: How to get Minimum or Maximum or Average?
                  Niranjan Banarjee

                  HI Carl,

                   

                  Have added the workbook.

                   

                  Can you please help in it.

                  • 6. Re: How to get Minimum or Maximum or Average?
                    Niranjan Banarjee

                    Hi Carl,

                     

                    were you able to  get any solution.

                    • 7. Re: How to get Minimum or Maximum or Average?
                      Carl Slifer

                      Hi Niranjan.

                       

                      You might need to change your table calcs, currently you are using a % of total and your values do not add to 100% This has to do with how you've partitioned and and split the database and how you've constructed your view. Yours is based on the Shelf Presence which means that everything within each sub header of Audit won't add to 100% but only all three combinations of Audit will.

                       

                      So the first thing to do is to change your Shelf Presence to running along table down. Then we will create three more fields based off of that one.

                      WINDOW_MAX([Your Field]), WINDOW_MIN([Your Field]). WINDOW_AVG([Your Field]).

                       

                      I've put all of these in your workbook as well on the measure values card. Created a new sheet and used LAST()=0 as a filter to only keep one row of data, then graphed on that.

                       

                      Now if you want to continue using Audit so that no column will add to 100% but rather every series of months will add to 100% if you include all the audit calcs then continue to use the table calc based off of 'Shelf Presence' as you had it before.

                       

                      Past this point I believe you need to really look into Table Calculations or grab someone for some light consulting time.

                       

                      Cheers,

                      Carl Slifer

                      InterWorks

                      • 8. Re: How to get Minimum or Maximum or Average?
                        Niranjan Banarjee

                        Thanks Carl for your suggestions.

                         

                        Definitely, will review and get back to you.

                        • 9. Re: How to get Minimum or Maximum or Average?
                          Niranjan Banarjee

                          HI Carl,

                           

                          Below are few of my concerns:

                           

                          • Implication of IS it Last? Parameter?
                          • I need the Window Max, Min, Avg of Not Present w/shelf tag.Believe you have got it in total table.

                           

                          Plz help.

                          • 10. Re: How to get Minimum or Maximum or Average?
                            Niranjan Banarjee

                            Basic requirement is to get the Max ,Min & Avg within all the sub categories of Shelf Presence.

                            Is it possible?

                            • 11. Re: How to get Minimum or Maximum or Average?
                              Carl Slifer

                              Niranjan,

                               

                              What are you referring to with

                              • Implication of IS it Last? Parameter?

                              It is a filter and it is on the filters shelf of both graphs. LAST() is a table calc that returns how many positions a cell is from the last in any given partition. By saying LAST() = 0 and only keeping the times it says 'true' we only get the last entry.

                               

                              I've shown both solutions, there are four tabs. 2 of these concern how you've initally set the table up, and 2 of them concern how I believe the table calculations should be based on what you've said. By including the crosstab I am able to illustrate that when using the Column you get the same value for the entire partition.

                              Hopefully you noticed that. When not using the column you do not get the same value for each entry down your time of day and this because of how you've addressed and partitioned things.   I think looking into table calcs and how they work will be very beneficial: Addressing and Partitioning

                               

                              The Column ones take into account the min, max, average for each month and each type of shelf presence. Please look at the two tabs that I've tagged with 'Line' from these you should be more than capable to figure out which one does what you need by seeing which gets the values that you believe to be the accurate representation of your data.

                               

                              I will not really be able to do much more in terms of explaining or fulfilling this need of yours. From what you've stated as the needs they have been fulfilled, At this point it would be having sit down to fully understand your needs as I don't think they have been communicated 100% at this point, or I'm failing to understand what you are trying to solve completely. I hate to play the sales game as I'm not a salesman. I would suggest some light consulting time in order to fully walk you through how partitioning and addressing works and so that you could get individualized attention in how to solve a very unique use case. Might I suggest InterWorks, my company: we can be contacted here and booked in fairly short increments. I am sure there are others who can do this as well. Tableau Expert Hotline | Get Help from the InterWorks Team

                               

                              Carl Slifer

                              InterWorks

                              1 of 1 people found this helpful
                              • 12. Re: How to get Minimum or Maximum or Average?
                                Niranjan Banarjee

                                Thanks Carl definitely good reading reference which will help me in understanding it better.