Chart Total

[gwilburn]

I have a chart displaying two sums(y-axis) by company(x-axis). The two sums are "current" and "late" orders.

My users are asking if there's a way to display the total of the sums (i.e. total number of orders) within the same chart.

I could do this with another chart, but... It would be nice to display this say in the legend.

[sean.lanktree]

The only way I can think to display an additional third value would be to add another series (combining the "current" and "late" orders).

[gwilburn]

The source is grouping two counts by company number... what the user is looking for is a total count (not grouped):

Basically a column total, not a row total (if that makes sense) of the data source below

COM_NBR	TODAY	LATEORDERS	ORDERS
029	2023-04-17	0	2
031	2023-04-17	1	0
035	2023-04-17	1	0
038	2023-04-17	0	22
044	2023-04-17	0	2
046	2023-04-17	0	20
054	2023-04-17	1	0
059	2023-04-17	31	0

I'm not sure how (or if) I can do that with my SQL data source.

SELECT
com_nbr,
current_date AS today,
count(
case
WHEN shp_date < current date
THEN com_nbr
END
) AS lateorders,
count(
case
WHEN shp_date >= current date
THEN com_nbr
END
) AS orders
FROM
oecmplnce
WHERE
(
status = '' and sbm_date = '0001-01-01'
)
GROUP BY
com_nbr,
current_date
ORDER BY
com_nbr

[gwilburn]

I believe I may have done it using UNION... Posted in case anyone else wishes to do the same

select 'TOTAL' as com_nbr,
current_date AS today,
count(
case
WHEN shp_date < current date
THEN com_nbr
END
) AS lateorders,
count(
case
WHEN shp_date >= current date
THEN com_nbr
END
) AS orders
FROM
oecmplnce
WHERE
(
status = '' and sbm_date = '0001-01-01'
)

union

SELECT
com_nbr,
current_date AS today,
count(
case
WHEN shp_date < current date
THEN com_nbr
END
) AS lateorders,
count(
case
WHEN shp_date >= current date
THEN com_nbr
END
) AS orders
FROM
oecmplnce
WHERE
(
status = '' and sbm_date = '0001-01-01'
)

GROUP BY
com_nbr,
current_date
ORDER BY
com_nbr;

[sean.lanktree]

So you are looking for a total count of orders and a total count of late orders, correct?

Maybe stack 2 KPI widgets along side the chart with this information?

[gwilburn]

So you are looking for a total count of orders and a total count of late orders, correct?

Maybe stack 2 KPI widgets along side the chart with this information?

Exactly... Your suggestion might be better... the data source editor does not like the addition of the UNION statement (even though the statement works fine fine in Run Sql Scripts).

Is there a video on stacking the widgets?

[sean.lanktree]

Just add a vertical container widget (under Utility Widgets) and then add your 2 KPI widgets within it.

[Johnny Major]

Hi Greg,

Can you supply the sql statement that the NAB SQL data source didn't like?

Thanks

[gwilburn]

Johnny - that would be the SQL statement I posted above.

[Johnny Major]

I tried this and it worked for me. Of course, I had to change the file name and column names. The only thing I had to remove was the ending semicolon since I copied it directly from your forum post.

By any chance, did you include the ending semicolon in the statement when you entered it in NAB?

SELECT 'TOTAL'      AS com_nbr,
       CURRENT_DATE AS today,
       COUNT(CASE
               WHEN shp_date < CURRENT DATE THEN com_nbr
             END)   AS lateorders,
       COUNT(CASE
               WHEN shp_date >= CURRENT DATE THEN com_nbr
             END)   AS orders
FROM   oecmplnce
WHERE  ( status = ''
         AND sbm_date = '0001-01-01' )
UNION
SELECT com_nbr,
       CURRENT_DATE AS today,
       COUNT(CASE
               WHEN shp_date < CURRENT DATE THEN com_nbr
             END)   AS lateorders,
       COUNT(CASE
               WHEN shp_date >= CURRENT DATE THEN com_nbr
             END)   AS orders
FROM   oecmplnce
WHERE  ( status = ''
         AND sbm_date = '0001-01-01' )
GROUP  BY com_nbr,
          CURRENT_DATE
ORDER  BY com_nbr

[gwilburn]

I'm trying to reproduce the error... I can't seem to do it (get it to work in Run SQL Scripts).

I forgot that I simplified the SQL statement before posting. I had to remove the vvin_Virtual() to put this into Run SQL Scripts. I also was joined to another file

I think this is the actual code... NOTE: The editor didn't wrap the statement as a CTE "tempt" using the code below.

SELECT
'TTL' AS com_nbr,
current_date AS today,
count(
case
WHEN shp_date < current date
THEN f1.com_nbr
END
) AS lateorders,
count(
case
WHEN shp_date >= current date
THEN f1.com_nbr
END
) AS orders
FROM
oecmplnce AS f1
-- LEFT JOIN tbfleads AS f2 ON f2.com_nbr = f1.com_nbr
WHERE
(
status = '' and sbm_date = '0001-01-01'
)
union
SELECT
f1.com_nbr AS com_nbr,
current_date AS today,
count(
case
WHEN shp_date < current date
THEN f1.com_nbr
END
) AS lateorders,
count(
case
WHEN shp_date >= current date
THEN f1.com_nbr
END
) AS orders
FROM
oecmplnce AS f1
-- LEFT JOIN tbfleads AS f2 ON f2.com_nbr = f1.com_nbr
WHERE
(
status = '' and sbm_date = '0001-01-01'
)
GROUP BY
com_nbr,
current_date
ORDER BY
com_nbr

The error is SQL0197 Column COM_NBR cannot be qualified and was coming from the JOIN to TBFLEADS above.

At this point it's not worth pursuing anymore...