0 votes
1 view
in BI by (39.1k points)

The following is easily done in SQL, but I am having a little trouble doing the same thing in Tableau just from the raw data.

The raw data is in the following format.

Date | Crimetype | Crimes

2015-06-01 | Violent | 5

2015-06-01 | Violent | 5

2015-06-02 | Misdemeanor | 3

2015-06-03 | Parking Related | 2

What I want to do is get by date, show the number of all crimes and the number of crimes that are 'Violent' or 'Misdemeanor', and then the percentage of crimes that are 'Violent' or 'Misdemeanor.'

This is super easy to do in SQL but I want to be able to do this in tableau from the raw data.

1 Answer

0 votes
by (14.1k points)
  • It is very easy to do in Tableau as well, and in an interactive way.

  • Simply drag SUM(Crimes) to the worksheet for the number of crimes.

  • For the percentage that are Violent or Misdemeanor, I can show you 2 approaches (there are many more, depending on what is your aim).

    • Simply create a field:
      SUM(IF Crimetype = 'Violent' OR Crimetype = 'Misdemeanor' THEN Crimes END) / SUM(Crimes)

This will give you the percentage of crimes that are either Violent or Misdemeanor

    • There is another way which is more interactive to do this,  is to create field to be used as filter:
      LOOKUP(ATTR(Crimetype),0)

  • When you use this as a filter (important, you need to have the crimetype info in the worksheet, and have the table calculation partition on Crimetype), as it is a table calculation, it filters after all other calculations.

  • In order to achieve what you need, you can use a simple Percent of Total table calculation on SUM(Crime). Moreover, you can make a quick filter with the Crimetypes you want to see.

  • Other way to do this is put Crimetype on the screen, then select one (or more) and choose to hide. It will not appear on screen, but it will be considered for calculations (same as the table calculation filter). But this method is not so interactive

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...