Comparison of data based on observation date ranges.

[jerpic]

I am using a PostgreSQL database.

I have a view called BOOKINGS with the following columns:

• BookingId: A unique identifier for a booking.
• PaymentDate: A timestamp representing the payment date of the booking.
• DepartureDate: A timestamp representing the departure date of the outbound flight.
• TotalPaid: A float8 value representing the total amount of the booking.
• Ligne: A text value representing the flight routing (e.g., CDG-NYC).

How can I display, on a histogram, the number of bookings per month of transport (based on DepartureDate) for an observation period (values of PaymentDate, e.g., from 01/01/2025 to 10/01/2025), as well as the number of bookings (for comparison by month of transport) for the same observation period in the previous year?

Any help, would be very much appreciated.

[dosubot[bot]]

Hey @jerpic! I'm a bot here to help you out with any bugs, questions, or contributions. Let's tackle this issue together!

To display the number of bookings per month of transport on a histogram in Apache Superset, and compare it with the same period in the previous year, follow these steps:

1. Create a SQL Query: First, create a SQL query to extract the number of bookings per month based on DepartureDate for the specified observation period (PaymentDate between 01/01/2025 and 10/01/2025) and for the same period in the previous year.

   SELECT
     DATE_TRUNC('month', "DepartureDate") AS month_of_transport,
     COUNT("BookingId") AS bookings_count
   FROM
     BOOKINGS
   WHERE
     "PaymentDate" BETWEEN '2025-01-01' AND '2025-01-10'
   GROUP BY
     month_of_transport
   UNION ALL
   SELECT
     DATE_TRUNC('month', "DepartureDate") AS month_of_transport,
     COUNT("BookingId") AS bookings_count
   FROM
     BOOKINGS
   WHERE
     "PaymentDate" BETWEEN '2024-01-01' AND '2024-01-10'
   GROUP BY
     month_of_transport;

2. Configure the Histogram in Superset:

   • Use the EchartsHistogramChartPlugin to create a histogram ^[1][2].
   • Set the month_of_transport as the x-axis and bookings_count as the y-axis.
   • Use the time_compare feature to overlay the data from the previous year for comparison ^[3].

3. Time Comparison Setup:

   • In the chart configuration, enable the time_compare control to specify the time shift for the previous year.
   • Set the comparison_type to display the actual values or differences as needed ^[3].

This setup will allow you to visualize and compare the number of bookings per month of transport for the specified observation period and the same period in the previous year on a single histogram.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[jerpic]

How can the "PaymentDate" date range be used as a filter for the histogram?

[dosubot[bot]]

To use the "PaymentDate" date range as a filter for the histogram in Apache Superset, you can follow these steps:

1. Add a Date Filter Control: Use the time_range control to filter the data based on the "PaymentDate" column. This control allows you to specify a date range for filtering.

2. Configure the Date Filter:

   • In the chart's control panel, add the time_range control and configure it to apply to the "PaymentDate" column.
   • You can specify the date range directly in the control, such as from '2025-01-01' to '2025-01-10'.

3. Link the Date Filter to the SQL Query:

   • Ensure that your SQL query uses the time_range filter to dynamically adjust the data based on the selected date range. This can be done by using a placeholder or variable in the query that gets replaced by the selected date range.

By setting up the time_range control in this way, you can filter the histogram data based on the "PaymentDate" date range, allowing for dynamic and interactive data exploration.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[jerpic]

Regarding this remark : "Ensure that your SQL query uses the time_range filter to dynamically adjust the data based on the selected date range. This can be done by using a placeholder or variable in the query that gets replaced by the selected date range.", could you adapt the SQL query below accordingly?

SELECT
  DATE_TRUNC('month', "DepartureDate") AS month_of_transport,
  COUNT("BookingId") AS bookings_count
FROM
  BOOKINGS
WHERE
  "PaymentDate" BETWEEN '2025-01-01' AND '2025-01-10'
GROUP BY
  month_of_transport
UNION ALL
SELECT
  DATE_TRUNC('month', "DepartureDate") AS month_of_transport,
  COUNT("BookingId") AS bookings_count
FROM
  BOOKINGS
WHERE
  "PaymentDate" BETWEEN '2024-01-01' AND '2024-01-10'
GROUP BY
  month_of_transport;

[dosubot[bot]]

To adapt your SQL query to use the time_range filter dynamically in Apache Superset, you can utilize Jinja templating. Here's how you can modify your query:

{% set time_filter = get_time_filter("PaymentDate", remove_filter=True) %}
{% set from_expr = time_filter.from_expr %}
{% set to_expr = time_filter.to_expr %}

SELECT
  DATE_TRUNC('month', "DepartureDate") AS month_of_transport,
  COUNT("BookingId") AS bookings_count
FROM
  BOOKINGS
WHERE
  "PaymentDate" >= {{ from_expr }}
  AND "PaymentDate" < {{ to_expr }}
GROUP BY
  month_of_transport
UNION ALL
SELECT
  DATE_TRUNC('month', "DepartureDate") AS month_of_transport,
  COUNT("BookingId") AS bookings_count
FROM
  BOOKINGS
WHERE
  "PaymentDate" >= {{ from_expr | date_add(-1, 'year') }}
  AND "PaymentDate" < {{ to_expr | date_add(-1, 'year') }}
GROUP BY
  month_of_transport;

In this query, the get_time_filter function is used to dynamically obtain the from_expr and to_expr based on the selected time_range in the Superset UI. The date_add function is used to adjust the date range for the previous year comparison ^[1][2].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[jerpic]

I changed the file config.py to set the flag "ENABLE_TEMPLATE_PROCESSING" to True. Then I launched this command :
docker compose -f docker-compose-non-dev.yml up
Unfortunately, Jinja templating is not activated.
In SqlLab, I get this error for the first line : 'Syntax error at or near "{"'
Is there something else to do?