Getting the filter values pre selected based on a particular condition in tableau - tableau

I have a workbook in which I have 2 filters, Dealer Name and Serial No.
Dealer Name is a single value list.
Based on the dealer name selected, I want to show that serial no which the dealer has ordered maximum times
Eg for Dealer D 100, the serial no ak101 appears 3 times and serial no ak102 appears 2 times.
So since count of ak101 is greater than count of ak102 for dealer D 100, I should get the serial no filter preselected to value : 'ak101' for dealer D 100
This is a little tricky but possible as I saw this on one of the blogs(link below). What I want to achieve is slightly different than what is shown here.
How to dynamically set default values for quick filters | Tableau Software
All excel and twbx files can be found here

Related

Rating bus stops based on attributes and mapping on Carto

I have a bus stop dataset that I want to plot onto Carto. The dataset includes columns of attributes about the bus stop and I want to create a new column called Rating which would rate based on those attributes (whether the bus stop has route maps or how pedestrian friendly it is).
First, I created a new column called rating using this sql code.
alter table metro_bus_stops
add rating int NOT NULL DEFAULT(0)
Then, I used the following code to add 3 points for the bus stops that has bike racks (attribute = bstp_has_bkrs)
update metro_bus_stops
SET rating = rating + 3
WHERE bstp_has_bkrs = 'Y'
I have 27 attributes total that would add or subtract points to the rating. I want to map the bus stop points based on these rating score on Carto eventually. Is there a better way to work with this rather than adding calculations for each attribute individually? I wonder if I could do this better in Python with a formula using if else statement.
This is the bus stop data I am using if it would help.
Any feedback would be appreciated!
You can combine any number of columns to your SQL statement, combining them with logical operators like AND and OR. This should be the easiest way to do it, something like:
update metro_bus_stops
SET rating = rating + 3
WHERE bstp_has_bkrs = 'Y'
OR attribute2 = value2 AND
AND attribute3 > value3
OR ...

Show group totals without showing individual values, with missing values

I am trying to create a report that looks like this:
An engineer goes to the site and takes readings periodically, which are then uploaded to the database. The view that returns data will return all rows that have been recorded, with each row having (amongst others), the following fields:
Zone ("A" - "D")
Work Area ("A1" - "A26", etc.)
Work Area ID
Percentage Complete
Caveats:
Multiple rows may have the same work area (i.e. multiple measurements as completion moves from 0 to 100 in increments of 20)
At any given time, some zones will have no data recorded for them
I have no data (no field in the view) for "Total Work Areas", so I have to manually capture these in the RDLC.
Currently, all percentage complete data are nulls, so I am substituting the work area ids so I can use max appropriately.
By grouping a row on "Zone", and a column on "WorkArea". I'm not too clear on how to do some of these groupings in terms of placement, but these two pictures should illustrate what I mean:
The first expression is =First(Fields!Zone.Value) and falls within the [zone] grouping; the second expression is =sum(Max(Fields!WorkAreaID.Value,"WorkArea1")) (WorkAreaID will be replaced by PercentageComplete) later. I set the first column's visibility to hidden, and end up with (based on the current data):
These are correct values.
Zone F was from a test entry, so can be ignored.
My questions now are:
how would I put in the "Total Work Areas" column with my hard-coded values, and
how do I include Zones B and C given that they have no recorded data?
As extra help, would I have to reconfigure this a lot to then add "Work Areas Complete" - I assume I can use the SUM function, something like =sum(Iif(Fields!PercComplete.Value = 100, Fields!PercComplete.Value, 0))?

Counting and finding sum of combination of Dimensional attributes

Got a field in Excel:
Treament Status
IT
IT
SD
USD
SD
SD
USD
I need to use a calculated field to find a Percentage of total using this mathematical formula, based on the number of times certain dimensional attributes appears in the column i.e. Count:
% of R = [Count(SD)+Count(IT)]/[Count(Total of everything in Treatment Status)]
I know how to use the COUNT and SUM functions but I'm trying to connect the dots here.
One simple approach without writing any calculations is
Put Treatment Status on the Row shelf (double click should do it)
Number of Records on the Text Shelf (double click on it for a shortcut)
Then right click on the Number of Records field on the marks card and select Quick Table Calc -> Percent of Total
If you want to combine the SD and IT rows, select both SD and IT, then use the paperclip icon to group them together
The effect is to generate a query like select count(1) from xxx group by TreatmentStatus, and then to compute the percentages in Tableau acting upon the query results.
You need the TOTAL function. This should do it.
% of R = [Count(SD)+Count(IT)]/TOTAL(COUNT([Treatment Status]))

Combining filtering and Dynamic Ranking in Tableau

My question is as follows: Suppose I have a data set where I have 4 fields
Date
Type
Amount (metric 1)
Sell Price (metric 2)
These 4 fields are displayed in a table in my Tableau dashboard. When I click on any item in the table, I would like text at the top to display the rank of both of the metrics and the Unique count of the metrics in my table as well as their values.
e.g. 'Amount = 32,500: Ranked 4 out of 342, Sell Price = $2.5: Ranked 2 out of 6' (I only have 6 differing Sell prices)
I would also like the implementation to be dynamic, so as when I filter on date, the result would adjust to suit e.g. 'Amount = 32,500: Ranked 1 out of 10, Sell Price = $2.5: Ranked 1 out of 4'
I have tried using RANK function, but whenever I click on the a row in my table, it effectively filters out all of the other data and sets the rank to 1.
Is there a way to do this?
I think you won't be able to do exactly what you describe.
As you correctly stated, the RANK() function gives you the rank in the current "Partition", that is the data that is actually displayed on your dashboard. If you exclude eg. the row with a rank of 1, the row with the rank 2 will now be 1.
There is no possibility to calculate this based on the rank in the data source (unless you do it outside of Tableau).
If you have the rank in your data source, you can create a dashboard with 4 different sheets (full table, Amount, Rank, Number of Records) and filter the last 3 based on the selection in the first.
As a workaround you could display these values in a tool tip.
Create a field [NumberofRecords] with the formula Size()
Drop [NumberofRecords], [Rank] and [Amount] onto the tool tip shelf
Organise them however you want
As an example it could look like this:
Chris,
Try to use the INDEX() functionality rather than the RANK(). For example if you are showing results for the year, your $32,500 value may be ranked 123 of 1000. When filtering to the particular day, that "Rank" value will change to its corresponding rank within that day. Not sure if that is exactly what you were looking for. Here is a tutorial video on the topic. Hope this helps.
Using Index for Rank in Tableau : Video Link
UPDATE:
For Row and Metric Ranking:
Add the Price Field to the Rows
Change the price field to an Attribute instead of a Measure
Make it Discreet
Click on the Price field in the Rows section, in the dropdown select "Quick Table Calculations" > Rank
For verification of the data, you can add another discrete dimension Price field to the right of the ranking calculation. Everything should work as expected from here.
Additionally, I generated a quick sample in the following link using the Tableau Superstore Sales Data.
Example Project Using Tableau Superstore Sales Data
Eric

Using same data set for two worksheets

I have a data set that looks like this:
ID RL Category
12345 High MSB
12345 High NRA
24680 Low MSB
12121 High NNN
I would like to make a pie chart whose slices are the RL column, but I don't want to double count the rows that are identical except for the Category column. For example, the High slice should = 2, not 3.
Thought I'd get it by using COUNTD() somehow but pretty much stuck.
Altering the data source or creating a new one isn't an option because I am using it in another worksheet and don't want to give up the functionality of the global filter that's available if your sheets share a source.
If you create a Calc field that is countd (ID) and then put the RL field on the detail button and then that countd on the angle button it should work.

Resources