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

[ramonvermeulen]

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. 

[Dawid]

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?

[ramonvermeulen]

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.