Goal: Pull monthly stats and data related to sales & realized revenue for different sales people in our company and create a unique dashboard for each sales person.
Steps: So far I have built the dashboard but upon building the dashboard, when I return to the app, the code output seems to be duplicated, triplicated & now whatever the heck you call 4x lol
This has happened multiple times and I have even spent time rebuilding these dashboards from scratch. I don't believe the issue is in my code, but I can't seem to explain or figure out why the out put is being generated multiple times.
Please let me know what I can provide to help troubleshoot the issue.
SELECT
TO_CHAR(mb."month", 'Mon YYYY') AS month_year,
TO_CHAR(mb.bookings, 'FM999,999,999,999.00') AS monthly_bookings,
TO_CHAR(
SUM(mb.bookings) OVER (
PARTITION BY
EXTRACT(
YEAR
FROM
mb."month"
)
ORDER BY
mb."month"
),
'FM999,999,999,999.00'
) AS year_to_date_bookings,
TO_CHAR(
SUM(mb_prior.bookings) OVER (
PARTITION BY
EXTRACT(
YEAR
FROM
mb_prior."month"
)
ORDER BY
mb_prior."month"
),
'FM999,999,999,999.00'
) AS prior_year_to_date_bookings,
TO_CHAR(
SUM(mb.bookings) OVER (
PARTITION BY
EXTRACT(
YEAR
FROM
mb."month"
)
ORDER BY
mb."month"
) / SUM(mb_prior.bookings) OVER (
PARTITION BY
EXTRACT(
YEAR
FROM
mb_prior."month"
)
ORDER BY
mb_prior."month"
),
'FM999,999,999,999.00'
) AS bookings_ratio
FROM
"MonthlyBookings" AS mb
LEFT JOIN "MonthlyBookings" AS mb_prior ON mb_prior."month" = mb."month" - INTERVAL '1 year'
WHERE
mb."month" >= '2017-02-01'::date
AND mb.sales_person = 'Anthony DiMicco'
ORDER BY
mb."month" DESC;
I guess I get that the JOIN query can create duplicate rows in the output but I don't understand why when I originally wrote the formula a couple of different times, I only got the data once, but then upon attempting to edit the formula the output now appears duplicated. It would seem illogical for that to be the case & the output should be the same every time you run the same code right?
It would seem illogical for that to be the case & the output should be the same every time you run the same code right?
Yes. That shouldn't happen, except if you have more data in your DB. We do very minimal calculation on top of sending the query to postgres to run; so I still suspect it's something with your query.
It's possible these are different rows but having the same aggregated values. For example, there are multiple rows in "MonthlyBookings" with the same year & month but different value in other columns. When selecting only a few columns, it hides the different attributes, so it looks like duplicates.
I suggest adding mb.* at the beginning or end of your SELECT statement to debug.
Thanks for the advice there. I suspect it was something like that as well. I re-wrote the formula and was able to get it working, just found that to be odd but it could be that both table have the same month and year in them or something like that and it got things confused based on that.
