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

# Sorting by excluded value

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. Anybody knows how to fix it?

Regards,

Elmurat

• ###### 1. Re: Sorting by excluded value

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

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

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

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

• ###### 5. Re: Sorting by excluded value

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

Amazing! Thank you very much Simon!

It is working correct now.

• ###### 7. Re: Sorting by excluded value

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

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

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

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

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)