Forum Discussion
Crime Analysis Table
I am trying to do a real time crime analysis table visual based off of a live sql query. I have a data table which has columns that provides the date (date_occurred) the crime took place, the uniform crime report (ucr) number and the description (UCR_description) for each UCR code.
| Date Occurred | UCR Code | Code Description |
| 1/1/20 | 110 | H |
| 1/1/20 | 210 | S |
| 1/1/20 | 740 | AU |
| 1/1/20 | 600 | B |
| 1/1/20 | 600 | B |
| 1/1/20 | 310 | R |
| 1/1/20 | 310 | R |
| 1/1/20 | 310 | R |
| 1/1/20 | 210 | S |
| 12/12/19 | 820 | T |
| 12/12/19 | 820 | T |
| 12/12/19 | 810 | T |
| 12/12/19 | 740 | AU |
| 12/12/19 | 900 | AR |
| 12/12/19 | 110 | H |
| 12/12/19 | 210 | S |
| 12/12/19 | 210 | S |
| 12/12/19 | 600 | B |
| 1/17/20 | 600 | B |
| 1/17/20 | 310 | R |
| 1/17/20 | 310 | R |
| 1/17/20 | 310 | R |
| 1/17/20 | 210 | S |
| 1/17/20 | 820 | T |
| 1/17/20 | 820 | T |
| 1/17/20 | 810 | T |
| 1/17/20 | 740 | AU |
| 1/17/20 | 900 | AR |
| 1/17/20 | 900 | AR |
I am trying to do a year to date comparision by each UCR code that would produce a table visual like this:
| UCR | Description | 2020 | 2019 | % Change |
| 110 | H | 1 | 1 | 0.00% |
| 210 | S | 1 | 2 | -50.00% |
| 310 | R | 6 | 0 | 600.00% |
| 600 | B | 3 | 1 | 200.00% |
| 740 | AU | 2 | 1 | 100.00% |
| 810 | T | 1 | 1 | 0.00% |
| 820 | T | 2 | 2 | 0.00% |
| 900 | AR | 2 | 1 | 100.00% |
I can get the table visual to work for YTD 2020 but can not figure out how to show the year to date comparision between 2020 and 2019. The year to date would always compair this year to last year.
Any help would be appreciated.
2 Replies
Anonymous there are lots of posts on comparing with Previous Year and the key is to add date/calendar dimension in your model. There are many posts on how you add date/calendar dimension and as it is a best practice to do all Time Intelligence calculations from this date/calendar dimension. This is your starting point and you will easily get it working using time intelligence functions.
I would 💖 Kudos 🙂 if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
