How to make a column chart value label display N/A for empty columns?

Hi, I have a column chart which displays the % of documents approved on time each month for the past 3 months by location. The value is the total that were approved on time out of the total documents, so a 0% represents that none of the documents were approved on time that month, whereas a blank bar represents that there were no documents for that month/office. For the blank bars I want the value labels to say N/A because without any bars it's really difficult to tell which month is which, but I'm not sure how to achieve this. Thoughts?

I did add some dummy data into the dataset so that even in months when an office doesn't have any documents, a row still exists for that office/month, with nulls for all of the document data including the approved_on_time field. In the document rows, approved_on_time is 1 if it was approved on time and 0 if it wasn't. I added the dummy data so that even when there are no documents for a particular office/month, the chart will still display all 3 months with no bars (rather than showing a No Results chart).

lring

Posted 1 year ago·Last reply 1 year ago

12 comments

DRay

·1 year ago · Edited

Hi lring,

Can you give this a try?

widget.on('beforeviewloaded', (scope, args) => {
	Object.keys(args.options.plotOptions).forEach((chartType) => {
		const origFn = args.options.plotOptions[chartType].dataLabels.formatter;
		args.options.plotOptions[chartType].dataLabels.formatter = function (point) {
			const self = this;
			return function (point) {
				const formattedValue = origFn.apply(this, point);
				const value = (parseInt(formattedValue) === 0 || isNaN(formattedValue)) ?  'N\\A' : formattedValue;
				return value;
			};
		}.call({
			original: true
		});
	})
})

image (13).png

image (14).png

lring

OP1 year ago

Interesting! That turned all of the labels to N\A, even the ones with actual values

lring

OP1 year ago

I don't speak javascript but I asked ChatGPT to rewrite it based on my goal and the current issue, and it actually worked:

widget.on('beforeviewloaded', (scope, args) => {
    Object.keys(args.options.plotOptions).forEach((chartType) => {
        const origFn = args.options.plotOptions[chartType].dataLabels.formatter;
        args.options.plotOptions[chartType].dataLabels.formatter = function (point) {
            const self = this;
            return function (point) {
                const formattedValue = origFn.apply(this, [point]); // Apply the original formatter

                // Convert the formatted value to a number
                const numericValue = parseFloat(formattedValue);

                // If the value is negative, display 'N/A'; otherwise, show the formatted value
                const value = (numericValue < 0 || isNaN(numericValue)) ? 'N/A' : formattedValue;

                return value;
            };
        }.call({
            original: true
        });
    });
});

So thank you for getting me 99% of the way there, and thank the robots for figuring out the last piece!

lring

OP1 year ago

DRay hi again, quick question; in the code you used here, does it still display that way when you save the dashboard as a PDF? My code works great on my dashboard and in the PDF Report Settings preview screen, but after downloading the PDF it loses the N/As from the widget script. Is there something else that I can add to the script to make sure they show up in the PDF export? Thanks!

DRay

·1 year ago

Hi lring,

The PDF export runs the report in a headless browser, so Widget scripts aren't applied. This is a current limitation of the product, but a solution is planned for the future.

DRay

·1 year ago

lring.

This post may help you though. https://community.sisense.com/t5/knowledge-base/ensuring-accurate-pdf-export-headers-when-programmatically/ta-p/25565

DRay

·1 year ago

Hi lring.

Did that KB doc I linked help?

DRay

·1 year ago

Hello lring,

Thank you for reaching out. I see your question hasn't gotten a response yet, so I'm asking internally to try and get you an answer. To be clear, you want the chart to display N/A instead of 0%, is that correct?

lring

OP1 year ago

Hi David, thanks for replying! That is not correct, what I'm looking for is a way to put N/A on those two blank areas where the red arrows are, because those are technically the "bars" for 8/24 and 9/24 for the Congress St office. The 0% means that there were some number of documents due 10/24 but none of them were approved on time, vs. the blank "bars" which means that there were no documents due for 8/24 or 9/24 for that office, so it's important that there's a distinction between them. Do you know of a good way to achieve this? Thanks!

DRay

·1 year ago

Hi lring,

Thank you for the clarification. I don't have a solution off the top of my head, but I'll see what I can find for you.