1. 

   Dev, Member

   Hello,

   I am currently working on a query to generate a stacked column chart that compares payments across corresponding months over different years. My query is structured as follows:

   WITH payments AS (
   SELECT
   p.payment_date,
   p.transaction_type,
   p.paid_amount,
   i.merchant_key
   FROM
   kennel_payment.invoice i
   INNER JOIN kennel_payment.payment p ON p.invoice_id = i.id
   WHERE
   i.merchant_key = inTenantKey
   AND i.deleted = FALSE
   AND i.status IN ('OPEN', 'CLOSED')
   AND i.type IN ('INVOICE', 'ESTIMATION')
   AND p.payment_date BETWEEN v_start_date AND v_end_date
   AND p.payment_status = 'APPROVED'

   UNION ALL

   SELECT
   p.payment_date,
   p.transaction_type,
   p.paid_amount,
   i.merchant_key
   FROM
   kennel_payment.invoice i
   INNER JOIN kennel_payment.payment p ON p.invoice_id = i.id
   WHERE
   i.merchant_key = inTenantKey
   AND i.deleted = FALSE
   AND i.status IN ('OPEN', 'CLOSED')
   AND i.type IN ('INVOICE', 'ESTIMATION')
   AND p.payment_date BETWEEN v_start_date_prev AND v_end_date_prev
   AND p.payment_status = 'APPROVED'
   )

   SELECT
   YEAR(payment_date) AS payment_year,
   DATE_FORMAT(payment_date, '%b, %Y') AS `Range`,
   SUM(CASE
   WHEN transaction_type IN ('Refund', 'Return', 'Void') THEN -paid_amount
   ELSE paid_amount
   END) AS `Total Payments`,
   MONTH(payment_date) AS payment_month
   FROM
   payments
   GROUP BY
   YEAR(payment_date),
   MONTH(payment_date),
   DATE_FORMAT(payment_date, '%b, %Y')
   ORDER BY
   payment_month ASC,
   payment_year ASC;

   The output of this query is similar to the following:

   June 2023 - $51,620
   June 2024 - $65,513

   I want to create a stacked column chart where corresponding months across different years are grouped together in a single bar. For example, June 2023 and June 2024 should be grouped in one bar, not separated by year.

   I've tried grouping the months, but I'm struggling to achieve the desired visualization. I'm looking for guidance on how to modify my query or chart settings to accomplish this.

   Here is an example of the type of chart I want to create: https://www.google.com/search?sca_esv=5982b43ca79cf003&sca_upv=1&rlz=1C1CHBF_enIN1051IN1051&q=stacked+column+and+bar&udm=2&fbs=AEQNm0AeMNWKf4PpcKMI-eSa16lJoRPMIuyspCxWO6iZW9F1Ns6EVsgc0W_0xN47PHaanAEtg26fpfc9gg2y1-ZsywNNidIzOA0khSyMN51n7r3LlDC9M1NYStuTRDcBUYQ58dKt-Q6SigUS4Yne5yDHLg0vPBr98Nz98twIaNcnWiKaD4QuEh93Q53sB-UkWP9OcfO5KeatY98HR7cDW9ZTjFpZV7kJtA&sa=X&ved=2ahUKEwjKyIH6vvKHAxXIcWwGHRMoEtYQtKgLegQIExAB&biw=1536&bih=730&dpr=1.25

   Could someone please guide me on how to adjust my query or chart configuration to group these months properly in the chart? Any help would be greatly appreciated.

   Thank you in advance for your assistance!
2. 

   myDBR Team, Key Master

   The basic format of the query should be:

   select 'dbr.chart', 'StackedColumn';
   
   select month(payment_date), year(payment_date) , sum(...)
   
   from yourdata
   
   group by month(payment_date), year(payment_date);
   
   

   This would put months in x-axis and data from each year's month in same column.

   --
   myDBR Team
3. 

   Dev, Member

   Thank you for the guidance.

Reply

You must log in to post.