(Financial) Dashboard with comparison between two days, and update filter based on another filter

Looker Q&A Looker
1

Hello! I am new to Looker, however not new to BI tooling in general. I have an OBT (one big table) loaded into Looker with ~30k financial fact records per day, on which I have an aggregated report. All records individually have an as_of_date available (on which I pivot the table).

The idea is that in the report you can select a base date (which by default is the last business day, or max date available in the OBT if that is easier to implement). And a comparison date, which by default should be the day before the last business day. The comparison date should always be before the base date.

I use a pivot table with field calculations to calculate the difference in moves between those two selected days, e.g. orders, revenue, profit went X % up or down compared from the base to the comparison date.

I am struggling to implement this in Looker in a correct manner, any suggestions? I am using a LookML dashboard, but already struggling to connect two date filters to one as_of_date (e.g. only one works), or dynamically setting default values for the filters.

Any suggestions, tips, or recommendations to implement this in a way which make the user experience as straight forward as possible are highly appreciated.

2

It’s hard to say without examples of your code and the output you’re currently getting.

How about if you don’t pivot and just select two separate dates from a filter?

3

Hi Dawid, you are right, I’ll provide some more context.

So the financial data also has some sort of book structure, which is always three levels deep, this is something that won’t change so I can build the report statically around that concept. My main goal is to make the dashboard as much as identical to the current (excel) dashboard that they use for this use-case. For that reason I did choose for a pivot table (maybe there are better solutions). My final output should look something like:

Base date selected: 06-11-2023 (ideally you can only select business days in the past OR dates available in the data)
Comparison date selected: 03-11-2023 (ideally this updates to the before last business day when the base date updates)
(because there is a weekend in-between)

For the diff fields I use table calculations, e.g.:

(pivot_index(${table.rev-sum}, 2) - pivot_index(${table.rev-sum},1)) / pivot_index(${table.rev-sum},1)

Sample:

as_of_date>
book_1 book_2 book_3 rev-sum-03-11-2023 rev-sum-06-11-2023 rev_diff ord-sum-03-11-2023 ord-sum-06-11-2023 ord_diff
A C G 1215 1351 11,2% 754 874 15.9%
A C H 5214 4812 … 374 412 …
A D I 1246 984 … 3712 5124 …
A E J 6423 5842 … 53215 75129 …
B F K 6732 7921 … 6342 4928 …
B F L 3463 4821 … 534 192 …

In reality column names are different, but this sample illustrates the use-case.

After reading some blogs I tried out the following solution, I don’t know if it is the way to go, however for now it works. Added the following two filters and dimension_groups to my view:

filter: report_date {
    type: date
    label: "Report Date"
  }

  filter: comparison_date {
    type: date
    label: "Comparison Date"
  }

  dimension_group: report_date_group {
    type: time
    timeframes: [date]
    sql: {% date_start report_date %} ;;
  }

  dimension_group: comparison_date_group {
    type: time
    timeframes: [date]
    sql: {% date_start comparison_date %};;
  }

Added two single date filters and added them to the LookML dashboard via listen:

listen:
      report_date: my_explore.report_date
      comparison_date: my_explore.comparison_date

Added following sql_always_where clause to my explore:

explore: my_explore {
  sql_always_where:
    {% if my_explore.report_date._is_filtered and my_explore.comparison_date._is_filtered %}
      ${as_of_date} = ${report_date_group_date} or ${as_of_date} = ${comparison_date_group_date}
    {% else %}
      1=1
    {% endif %}
  ;;
  view_name: my_explore {
    label: "My Explore"
  }
}

It is working in a sense that I can select two different dates and the comparison is being showed. The only problem is I cannot find a way to sort the column order for the pivot results. E.g. I can hard code them for two days in this way via the dashboard yaml:

column_order: [...,
2023-11-05_my_view.rev_sum,
2023-11-01_my_view.rev_sum, rev_diff, 2023-11-05_my_view.ord_sum,
2023-11-01_my_view.ord_sum, ord_diff,
...]

However when I change the date filters the orders gets reset since I have other days selected than 05/11 or 01/11.

Another problem I still run into is automatically setting the base date (report date) to the last business day, and the comparison date to the second last business day. And automatically updating the comparison date when the base date (report date) updates.