11 Replies Latest reply on Jan 9, 2015 10:28 AM by Zahira Hosein

    How to get next smallest value?

    Zahira Hosein

      Hello!

      Are there any (efficient) Tableau functions that can be used to find the next smallest value?

      To provide some background, we have 5 plans and i am calculating the cost of each plan for each customer. I then want to find the smallest and next smallest cost per customer (ie, best 2 plans per customer). Please see attached workbook. In this sample, Plan A-E are just listed, but in reality they are more complex calculations. I am using the Min function to get the smallest value, and 'Next Smallest' is calculated using 'Smallest'..which is inefficient and taking too long in my actual workbook. So I was wondering if there was a more efficient way to get next smallest value...

       

      Thanks!

        • 1. Re: How to get next smallest value?
          Łukasz Majewski

          If you had your data layout changed you could use rank_unique function as in the attached workbook.

          • 2. Re: How to get next smallest value?
            Simon Runc

            hi Zahira,

             

            Yes I see your issue, although the work around function you've created is pretty cool, even if inefficient!!

             

            What is the data source for this? The reason I ask is that if you could re-shape the data you could use a Table Calc. The reason you need such a huge nested formula is that the Plans are different measures.

             

            In the attached I've used the Data Re-shaper in Excel to de-pivot the data, which now looks like this, although this could be done in SQL...etc.

             

             

            CustomerPlanValue
            1000Plan A300
            1000Plan B250
            1000Plan C600
            1000Plan D500
            1000Plan E400
            2000Plan A600
            .......

            Once in this form, I can use the Index() function to 'Rank' the entries, and then use the filter to only bring back only index 1 & 2 (I've also created a quick if statement to label these 'smallest' and 'next smallest'. The Index() function is a table calc, and as such I can control how it calculates. In this case I've run it by Plan, restarting at every customer. I've also sorted the index by Ascending Sum of Value.

             

            Hope this a) Helps, and b) makes sense.

            • 3. Re: How to get next smallest value?
              Joe Oppelt

              I didn't open the workbook because I don't have time to take a look at what you are doing, but have you considered doing a sort-ascending, and then limiting the viz to INDEX()<=2  ?

              • 4. Re: How to get next smallest value?
                Vladislav Grigorov

                Hi,

                 

                Have a look if the attached sparks some ideas. Basically I used custom SQL to reshape your data, as what you have as 5 different plans is in fact the cost for the customer of each of these plans. Then I ranked them in ascending order and just kept the 2 best ones for each customer.

                 

                Best,

                 

                Vlad

                • 5. Re: How to get next smallest value?
                  Zahira Hosein

                  Hi! thanks for this suggestion. However, I cant change the data because i am using a sql script that pulls average customer local and intl mins, sms, data, roaming...ect. In Tableau, I put all these variables into Plans A-E to calculate what each plan would cost the customer, and then try to get the best two. Thanks, and please let me know if you have any other suggestions

                  • 6. Re: How to get next smallest value?
                    Zahira Hosein

                    Hi Simon,

                     

                     

                    Yes your calculations are helpful and make sense, thank you! I am sure that I will use them in future. But I wouldnt be able to reshape the data to look like you have in your attached workbook though:/ ..My data source is a sql script that pulls average customer local and intl mins, sms, data, roaming...ect. In Tableau, I then put all these variables into Plans A-E to calculate what each plan would cost the customer, and then try to get the best two. Hence the reason that the  Plans are different measures. Thanks though, and let me know if you think of anything else.

                    • 7. Re: How to get next smallest value?
                      Łukasz Majewski

                      Share your sql script and i am sure it can be modified to achieve this.

                      • 8. Re: How to get next smallest value?
                        Zahira Hosein

                        Thank you, but my data source is a sql script that pulls average customer local and intl mins, sms, data, roaming...ect. In Tableau, I then put all these variables into Plans A-E to calculate what each plan would cost the customer, and then try to get the best two.

                        • 9. Re: How to get next smallest value?
                          Zahira Hosein

                          Hi, attached is a sample of the source data (that the sql script extracts from a combination of tables). Within Tableau, it uses this data to calculate Plans A-E, from which I want to get the smallest, and next smallest.

                          • 10. Re: How to get next smallest value?
                            Daniel Ong

                            How about extracting the data out?

                             

                            That way all the calculations could be materialized.

                             

                            You could also tune some of the calculations, that might improve the speed.

                             

                            For example, what you did was comparing a to b to c to d to e

                            which is min(min(min(min([Plan A],[Plan B]),[Plan C]),[Plan D]),[Plan E])

                             

                            What you can do is split the list to compare in twos first, so

                            min(min(min([Plan A],[Plan B]), min([Plan C],[Plan D])),[Plan E])

                            That will save one recursive step, and multiply that by the number of rows you have.. might be worth a try.

                             

                            Attached workbook.

                            1 of 1 people found this helpful
                            • 11. Re: How to get next smallest value?
                              Zahira Hosein

                              Hi Daniel,

                               

                              I did as you suggested with splitting the list to get 'min', didn't think of this before.  However its still taking a bit long to execute.

                              I can't extract the data out though.. because I used a lot of data blending to get the desired results

                               

                              Thank you