11 Replies Latest reply on Sep 5, 2018 5:19 AM by Simon Runc

    Sorting by excluded value

    Elmurat Bavlankulov

      Hi All,

       

      I have a date field, from which I have created a calculated field, where I will get Today, Yesterday and Older days.

       

      In my table, I need to see only today and yesterday, so I put my calculated field into columns (in Day format), then Exclude the Older days with right click.

       

      But when I sort my rows with my metric. It is sorting me values by the excluded value Old Days. But I need to sort it by Today.

       

      So when I exclude the Old Days, the sorting is not correct.

       

      Screen Shot 2018-09-05 at 12.09.02.png

       

       

      Anybody knows how to fix it?

       

       

      Regards,

      Elmurat

        • 1. Re: Sorting by excluded value
          Simon Runc

          hi Elmurat,

           

          One way to do this is to create a "Today Sales" field and sort on that.

           

          something like

           

          IIF([Date] = TODAY(),[Sales],NULL)

           

          Hope that does the trick and makes sense.

          1 of 1 people found this helpful
          • 2. Re: Sorting by excluded value
            Elmurat Bavlankulov

            Hi Simon,

             

            Thanks for your reply.

             

            It worked.

             

            But I am facing one more issue.

            Which is my Metric Selection calculated field. So I have a parameter, which lets user select a metric to show in the table. Let's say Sales, Profit, Bookings (count distinct of booking id).

             

            So I put your calculation into CASE statement,

             

            Case [Metrics Selector]

            WHEN 'Sales' THEN

            IIF([Date Parameter] =[Dim Bookingdate Id],[Sales],NULL)

            WHEN  'Profit' THEN

            IIF([Date Parameter] =[Dim Bookingdate Id],[Profit],NULL)

            WHEN 'Bookings' THEN

            IIF([Date Parameter] =[Dim Bookingdate Id],[Bookings],NULL)

            END

             

            but this is giving me error of agg and non-agg mixing.

             

            Is there any work around?

            • 3. Re: Sorting by excluded value
              Simon Runc

              Are any of the 3 measures in your formula aggregates (i.e. wrapped in a SUM, COUNT, AVG....etc.)? or are they just the "raw" row level field?

              • 4. Re: Sorting by excluded value
                Elmurat Bavlankulov

                yes, "Bookings", which is Count Distinct of booking IDs.

                • 5. Re: Sorting by excluded value
                  Simon Runc

                  OK then this should work

                   

                  Case [Metrics Selector]

                  WHEN 'Sales' THEN

                  SUM(IIF([Date Parameter] =[Dim Bookingdate Id],[Sales],NULL))

                  WHEN  'Profit' THEN

                  SUM(IIF([Date Parameter] =[Dim Bookingdate Id],[Profit],NULL))

                  WHEN 'Bookings' THEN

                  COUNTD(IIF([Date Parameter] =[Dim Bookingdate Id],[Booking ID],NULL))

                  END

                   

                  Notice I've wrapped the first 2 in SUM aggregates, and the final one (bookings) is doing the (row level) IIF on [Booking ID] and then aggregating it (using COUNTD)

                  2 of 2 people found this helpful
                  • 6. Re: Sorting by excluded value
                    Elmurat Bavlankulov

                    Amazing! Thank you very much Simon!

                    It is working correct now.

                    • 7. Re: Sorting by excluded value
                      Elmurat Bavlankulov

                      Simon, if possible, can you help me with second part of my dashboard?

                       

                      In there, I have a table, where I show share from total (Percent of Total, Table Down) of selected metric.

                       

                      CASE [Metrics Selection]

                      WHEN 'Sales' THEN SUM([Sales])/TOTAL(SUM([Sales]))

                      WHEN 'Profit' THEN SUM([Profit])/TOTAL(SUM([Profit]))

                      WHEN 'Bookings' THEN [Bookings]/TOTAL([Bookings])

                      END

                       

                      so this works fine.

                       

                      But what I need is, on the Today column, I need to show conditional formatting:

                       

                      if share decreased(compared to yesterday), today's number will be red, if increased, it will be normal color(black).

                       

                      The problem is, my calculated field is already showing as a table calculation, so I cannot further make it table calculation (Percent of Total, Table Down) and put it into color.

                      • 8. Re: Sorting by excluded value
                        Simon Runc

                        So this gets a little more advanced!

                         

                        First I created the SoB Calculation

                        [Sales - SoB]

                        SUM([Sales])

                        /

                        TOTAL(SUM([Sales]))

                         

                        Then I created the change

                        [Sales - SoB Change]

                        [Sales - SoB]

                        -

                        LOOKUP([Sales - SoB],-1)

                         

                        Now as we have the SoB "nested" in the SoB Change, we get the option to set up the 2 table calculations differently (when you bring the SoB Change onto the canvas there is a drop down on the Table Calc setup where you can select each table calc and set up differently. I set these up like

                         

                         

                        and finally, as yesterday will be NULL (there is now return for the LOOKUP(...,-1) for this, we need to set up a further calculation to colour everything correctly

                         

                        [SoB change colour]

                        IF [Sales - SoB Change]>=0 THEN 'Blue'

                        ELSEIF [Sales - SoB Change]<0 THEN 'Red'

                        ELSE 'Black'

                        END

                         

                        hope that makes a bit of sense.

                        • 9. Re: Sorting by excluded value
                          Elmurat Bavlankulov

                          Simon,

                           

                          Unfortunately it is not working for me, Simon.

                           

                          So in this worksheet I again have the Metric Selection.

                           

                          the part of creation SoB Calculation worked fine:

                           

                          CASE [Metrics Selection]

                          WHEN 'Sales' THEN SUM([Sales])/TOTAL(SUM([Sales]))

                          WHEN 'profit' THEN SUM([profit])/TOTAL(SUM([profit]))

                          WHEN 'Bookings' THEN [Bookings]/TOTAL([Bookings])

                          END

                           

                           

                          But the creating the Change part calculation is giving me error:

                           

                           

                          CASE [Metrics Selection]

                          WHEN 'Sales' THEN SUM([Sales] - LOOKUP([Sales],-1))

                          WHEN 'profit' THEN SUM([profit] - LOOKUP([profit],-1))

                          WHEN 'Bookings' THEN ([Bookings] - LOOKUP([Bookings],-1))

                          END

                           

                          I tried it without wrapping, with wrapping, that way, this way, but always giving error.

                          • 10. Re: Sorting by excluded value
                            Elmurat Bavlankulov

                            I also tried this for Change:

                             

                            [Share from Total]-LOOKUP([Share from Total],-1)

                             

                            it is not giving me error, but in the table it is showing not completely correct,

                             

                            some categories are showing both days red, some categories are showing both days black, which I don't understand why,

                             

                            and in some rows, it is comparing two days, and the smaller value marks red, it means if there was an increase from yesterday to today, it will mark yesterday red, which I don't need, I need to mark red if only today decreased compared to yesterday.

                            • 11. Re: Sorting by excluded value
                              Simon Runc

                              So I thought you wanted the change in Share of Sales, not the change in Sales.

                               

                              For Change in Sales you'll need

                               

                              SUM([Sales] - LOOKUP(SUM([Sales]),-1))

                               

                              Lookup (like total) is a Table Calculation, which you can think of as an Aggregate of an Aggregate, so has to be performed on an Aggregate (in this case SUM)

                               

                              With regards the colouring. Yes it will do this, as it's applying the colour to every "cell" (so for both days...we can't tell Tableau, in this set up, to only apply the colour to one day), in the case of the first day, when it goes back 1 day (-1) there is no 2 days back so it returns a NULL. Depending on the colour pill Tableau will usually colour a NULL on the center point of a continuous colour scheme. This is why I added the

                               

                              [SoB change colour]

                              IF [Sales - SoB Change]>0 THEN 'Blue'

                              ELSEIF [Sales - SoB Change]<0 THEN 'Red'

                              ELSE 'Black'

                              END

                               

                              So we then can choose the colour for Increase [Sales - SoB Change]>0 ; decrease [Sales - SoB Change]<0, or for No Change (OR NULL) the else statement makes it black (so looks like un-coloured text)