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

[lring]

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).

[DRay]

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]

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]

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.

[DRay]

Hi @lring,

We aren't able to provide a specific solution to this without a deeper dive. I can connect you with a technical resource that can work with you directly. Would you like me to do that?

[lring]

What would that entail?

I guess a more basic question to solve my problem would be: is there a way to have the chart value labels display a string rather than a number, and/or is there a way to use logic to change the value label based on the actual value? The formula that my chart uses as the value is SUM(Total approved_on_time)/(COUNT(event_id) - 1), but by changing it to CASE WHEN ISNULL(SUM([Total apr_on_time])/(COUNT([event_id]) - 1)) THEN -9.99 ELSE SUM([Total apr_on_time])/(COUNT([event_id]) - 1) END, I can have the blank columns display as -999% (or any other random percent I want), so if there was a way to edit the widget script to change any -999% value labels to N/A then that would solve the problem. Or if there was a way to write the value formula to output N/A instead of -9.99, but it gives me an error when I try that (presumably because it's a non-numerical value). Like if I use this as the formula it says Unrecognized Metadata Identifier N: CASE WHEN ISNULL(SUM([Total apr_on_time])/(COUNT([event_id]) - 1)) THEN 'N/A' ELSE SUM([Total apr_on_time])/(COUNT([event_id]) - 1) END. 

[DRay]

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
		});
	})
})

 

 

[lring]

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

[lring · Accepted solution]

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]

@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]

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]

@lring.

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

[DRay]

Hi @lring.

Did that KB doc I linked help?