I have a project where i need to show the amount in Thousand as K, Millions as M, and Billions as B in different segments such as Chart, Pivot Table

[Rupesh]

I have a project where i need to show the amount in Thousand as K, Millions as M, and Billions as B in different segments such as Chart, Pivot Table

[Volomeister]

Hi Rupes,

I believe it is not possible using standard Caspio tools, but it is doable with a bit of JavaScript customization.
Here is what you can do tabular report:

1. Add this code snippet to the header of your tabular report:
 

<script>

const formatNumber = new Intl.NumberFormat('en-GB', {
  notation: "compact",
  compactDisplay: "short"
})
</script>

2. Add an HTML block to your report page and this code inside the block:
 

<div id="tbl-cell[@field:AutoNum#]">

<script>

var num = parseFloat('[@field:Numbers]');

document.querySelector( '#tbl-cell[@field:AutoNum]').innerHTML = formatNumber.format(num )

</script>

</div>

Where @field:AutoNum must be substituted with the name of your unique table field and @field:Numbers must be substituted with the field where you store numbers.

Here you can check how it looks:

https://c7eku786.caspio.com/dp/7f80b000963021119fbb4d14bed1

Making it work for charts and pivot tables would require a bit different approach. If you can share a mock-up page with some data where you wish to change number formatting, it could help me to find a solution

[Rupesh]

Sure let me try this for datapage and then I will provide mock-up pages for chart and pivot

[Rupesh]

and one more thing I need to show data with 2 place of decimal such as 2.42 M

[Rupesh]

Please find the below mock-up for charts and pivot

[Rupesh]

22 hours ago, Volomeister said:

Hi Rupes,

I believe it is not possible using standard Caspio tools, but it is doable with a bit of JavaScript customization.
Here is what you can do tabular report:

1. Add this code snippet to the header of your tabular report:
 

<script>

const formatNumber = new Intl.NumberFormat('en-GB', {
  notation: "compact",
  compactDisplay: "short"
})
</script>

2. Add an HTML block to your report page and this code inside the block:
 

<div id="tbl-cell[@field:AutoNum#]">

<script>

var num = parseFloat('[@field:Numbers]');

document.querySelector( '#tbl-cell[@field:AutoNum]').innerHTML = formatNumber.format(num )

</script>

</div>

Where @field:AutoNum must be substituted with the name of your unique table field and @field:Numbers must be substituted with the field where you store numbers.

Here you can check how it looks:

https://c7eku786.caspio.com/dp/7f80b000963021119fbb4d14bed1

Making it work for charts and pivot tables would require a bit different approach. If you can share a mock-up page with some data where you wish to change number formatting, it could help me to find a solution

Hi, Just wanted to inform you that it is not working for me. please suggest

[futurist]

Hi @Rupesh,

You can try doing the following:

edit your DataPage, go to Configure Results Page Fields, add a Header and Footer, select the Header and disable HTML editor, and paste the following:

<script>

function intToString (value) {
if (num >= 1000000000) {
        return (num / 1000000000).toFixed(1).replace(/\.0$/, '') + 'G';
     }
     if (num >= 1000000) {
        return (num / 1000000).toFixed(1).replace(/\.0$/, '') + 'M';
     }
     if (num >= 1000) {
        return (num / 1000).toFixed(1).replace(/\.0$/, '') + 'K';
     }
     return num;
}

</script>

 

Add an HTML block and paste the following:

<div id="tbl-cell[@field:number_id]"><script>

var num = [@field:amount];

document.querySelector( '#tbl-cell[@field:number_id]').innerHTML = intToString(num)

</script></div>

 

Make sure to replace "number_id" in [@field:number_id] with your ID field in the table, and replace "amount" in "[@field:amount]" with your field that you want to convert to K, M, B, etc. Make sure to do this for all instances, or else the code will not work.

 

[Rupesh]

Thanks for sharing input, just wanted to check that what is All Instances mean? 

[futurist]

11 hours ago, Rupesh said:

Thanks for sharing input, just wanted to check that what is All Instances mean? 

Hi @Rupesh,

What i meant by that is, using the code I provided above, make sure you replace all instances or occurences of "number_id" in [@field:number_id] with your ID field in the table, and replace  all instances or occurences of "amount" in "[@field:amount]" with your field that you want to convert to K, M, B, etc.

So, let's say your ID field is named Booking_ID and the field that you wanna convert to K, M, B etc is named Booking_Amt, your code on the HTML block should be:

 

<div id="tbl-cell[@field:Booking_ID]"><script>

var num = [@field:Booking_Amt];

document.querySelector( '#tbl-cell[@field:Booking_ID]').innerHTML = intToString(num)

[Rupesh]

Thank you for the clarification, any idea how to do the same for Chart and Pivot? 

[futurist]

Hi @Rupesh,

 

For Pivot this is how you do it:

 

1. Edit your DataPage and go to Configure Pivot Table Fields.

2. Add a Header and Footer, select the Header  (disable HTML editor) and paste the ff:

<script>

function intToString (num) {
if (num >= 1000000000) {
        return (num / 1000000000).toFixed(1).replace(/\.0$/, '') + 'G';
     }
     if (num >= 1000000) {
        return (num / 1000000).toFixed(1).replace(/\.0$/, '') + 'M';
     }
     if (num >= 1000) {
        return (num / 1000).toFixed(1).replace(/\.0$/, '') + 'K';
     }
     return num;
}

</script>

 

3. on the Footer, disable HTML editor and paste the ff:

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {
const cells = document.querySelectorAll(".cbResultSetTableCellNumberDate");

[].forEach.call(cells, function(cell, i) {
var num = cell.innerHTML;
cell.innerHTML = intToString(num);
});

const cells2 = document.querySelectorAll(".cbResultSetTotalsDataCellNumberDate");

[].forEach.call(cells2, function(cell, i) {
var num = cell.innerHTML;
cell.innerHTML = intToString(num);
});

});
</script>

[futurist]

For charts:

 

1. On Header:

 

<script>

function intToString (num) {
if (num >= 1000000000) {
        return (num / 1000000000).toFixed(1).replace(/\.0$/, '') + 'G';
     }
     if (num >= 1000000) {
        return (num / 1000000).toFixed(1).replace(/\.0$/, '') + 'M';
     }
     if (num >= 1000) {
        return (num / 1000).toFixed(1).replace(/\.0$/, '') + 'K';
     }
     return num;
}

</script>

 

2. On Footer:

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {

var intervalId = window.setInterval(function(){
const cells = document.querySelectorAll(".highcharts-yaxis-labels text");

if (cells === null) {
} else {
clearInterval(intervalId);
[].forEach.call(cells, function(cell, i) {

var num = (cell.innerHTML);
cell.innerHTML = intToString(parseIntx(num));
});
}

}, 100);

});
</script>

 

[Rupesh]

I am not getting proper format of Number/currency as K, M, and B. Please suggest, am i doing anything wrong?

[Rupesh]

On Pivot also, i am not getting the format of Number/currency as K, M, and B.

[futurist]

 

On 7/22/2022 at 7:59 PM, Rupesh said:

I am not getting proper format of Number/currency as K, M, and B. Please suggest, am i doing anything wrong?

Hi @Rupesh,

It seems like it's because of the formatting of your numbers to currency. I have initially set up my code to apply if no formatting is set up for the values of y axis. since we're trying to shorten the number, i think ultimately what you would only need for the formatting is the dollar sign, so i modified the code to add that dollar sign along with the shortened number. 

1. On your DataPage, select "Values(Y Axis)". Set the Formatting to "None" (this is vital because again, the code I have only works for those with no formatting). Do the same for X Axis as necessary.

2. On the Header, paste the following:

<script>

function intToString (num) {
if (num >= 1000000000000000) {
        return (num / 1000000000000000).toFixed(1).replace(/\.0$/, '') + 'G';
     }
if (num >= 1000000000000) {
        return (num / 1000000000000).toFixed(1).replace(/\.0$/, '') + 'T';
     }

if (num >= 1000000000) {
        return (num / 1000000000).toFixed(1).replace(/\.0$/, '') + 'B';
     }
     if (num >= 1000000) {
        return (num / 1000000).toFixed(1).replace(/\.0$/, '') + 'M';
     }
     if (num >= 1000) {
        return (num / 1000).toFixed(1).replace(/\.0$/, '') + 'K';
     }
     return num;
}

</script>

 

3. On the Footer, paste the following:

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {

var intervalId = window.setInterval(function(){
const cells = document.querySelectorAll(".highcharts-yaxis-labels text");
const amounts = document.querySelectorAll("g[class*='highcharts-label highcharts-data-label highcharts-data-label-color']  text tspan");

if (cells === null) {
} else {
clearInterval(intervalId);
[].forEach.call(cells, function(cell, i) {
cell.setAttribute("value", cell.innerHTML);
var num = cell.innerHTML;
cell.innerHTML = "$" +   intToString(num);
});

[].forEach.call(amounts, function(am, i) {

am.childNodes.forEach(function(node){
  // Text nodes are nodeType: 3
  if(node.nodeType === 3 && node.nodeValue !== ""){
var num = node.nodeValue.trim()
am.parentNode.innerHTML ="$" +  intToString(num);

  }
});

});

}

}, 500);

});

var intervalId = window.setInterval(function(){
const legend = document.querySelectorAll("button[aria-label*='Show']");

if (legend === null) {
} else {
clearInterval(intervalId);
[].forEach.call(legend, function(elem, i) {
legend[i].addEventListener('click', function(event) {
const cells = document.querySelectorAll(".highcharts-yaxis-labels text");
[].forEach.call(cells, function(cell, i) {
var num = cell.innerHTML;
if(num.substring(0, 1) !==  '$'){
cell.innerHTML = "$" +   intToString(num);
}
});
});
});

}

}, 100);

</script>

 

Sample DP: https://c1hch576.caspio.com/dp/db26a00081997f921d5e4eafa668

 

[futurist]

On 7/22/2022 at 8:04 PM, Rupesh said:

On Pivot also, i am not getting the format of Number/currency as K, M, and B.

For the Pivot, same thing, you would have to get rid of the formatting if there's any.

1. On your DataPage on Configure Pivot Table Fields screen, select the field under Values and get rid of the Formatting (should be none)

2. On the Header, paste the ff (this has a slight difference compared to the code for the Chart because this has a condition that addresses zero values; the Chart automatically appends 0 to them but the Pivot does not. And this is necessary because on the Footer, I manually appended the dollar sign to the beginning of every value, so it looks kinda weird for zero values to have just "$"):

<script>

function intToString (num) {
if (num >= 1000000000000000) {
        return (num / 1000000000000000).toFixed(1).replace(/\.0$/, '') + 'G';
     }
if (num >= 1000000000000) {
        return (num / 1000000000000).toFixed(1).replace(/\.0$/, '') + 'T';
     }

if (num >= 1000000000) {
        return (num / 1000000000).toFixed(1).replace(/\.0$/, '') + 'B';
     }
     if (num >= 1000000) {
        return (num / 1000000).toFixed(1).replace(/\.0$/, '') + 'M';
     }
     if (num >= 1000) {
        return (num / 1000).toFixed(1).replace(/\.0$/, '') + 'K';
     }
     if (num == '') {
        return '0';
     }
     return num;
}

</script>

 

3. On the Footer, paste the following:

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {
const cells = document.querySelectorAll(".cbResultSetTableCellNumberDate");

[].forEach.call(cells, function(cell, i) {
var num = cell.innerHTML;

if(num.substring(0, 1) !==  '$'){
cell.innerHTML = "$" +   intToString(num);
}

});

const cells2 = document.querySelectorAll(".cbResultSetTotalsDataCellNumberDate");

[].forEach.call(cells2, function(cell, i) {
var num = cell.innerHTML;
if(num.substring(0, 1) !==  '$'){
cell.innerHTML = "$" +   intToString(num);
}
});

});
</script>

[Rupesh]

1 hour ago, futurist said:

 

Hi @Rupesh,

It seems like it's because of the formatting of your numbers to currency. I have initially set up my code to apply if no formatting is set up for the values of y axis. since we're trying to shorten the number, i think ultimately what you would only need for the formatting is the dollar sign, so i modified the code to add that dollar sign along with the shortened number. 

1. On your DataPage, select "Values(Y Axis)". Set the Formatting to "None" (this is vital because again, the code I have only works for those with no formatting). Do the same for X Axis as necessary.

2. On the Header, paste the following:

<script>

function intToString (num) {
if (num >= 1000000000000000) {
        return (num / 1000000000000000).toFixed(1).replace(/\.0$/, '') + 'G';
     }
if (num >= 1000000000000) {
        return (num / 1000000000000).toFixed(1).replace(/\.0$/, '') + 'T';
     }

if (num >= 1000000000) {
        return (num / 1000000000).toFixed(1).replace(/\.0$/, '') + 'B';
     }
     if (num >= 1000000) {
        return (num / 1000000).toFixed(1).replace(/\.0$/, '') + 'M';
     }
     if (num >= 1000) {
        return (num / 1000).toFixed(1).replace(/\.0$/, '') + 'K';
     }
     return num;
}

</script>

 

3. On the Footer, paste the following:

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {

var intervalId = window.setInterval(function(){
const cells = document.querySelectorAll(".highcharts-yaxis-labels text");
const amounts = document.querySelectorAll("g[class*='highcharts-label highcharts-data-label highcharts-data-label-color']  text tspan");

if (cells === null) {
} else {
clearInterval(intervalId);
[].forEach.call(cells, function(cell, i) {
var num = cell.innerHTML;
cell.innerHTML = "$" +   intToString(num);
});

[].forEach.call(amounts, function(am, i) {

am.childNodes.forEach(function(node){
  // Text nodes are nodeType: 3
  if(node.nodeType === 3 && node.nodeValue !== ""){
var num = node.nodeValue.trim()
am.parentNode.innerHTML ="$" +  intToString(num);

  }
});

});

}

}, 100);

});
</script>

 

Sample DP: https://c1hch576.caspio.com/dp/db26a000463a2944d65c4edb8ade

 

Thanks for sharing these details however in your sample still chart lable is not showing as K or M or B, please suggest

[Rupesh]

5 hours ago, futurist said:

 

Hi @Rupesh,

It seems like it's because of the formatting of your numbers to currency. I have initially set up my code to apply if no formatting is set up for the values of y axis. since we're trying to shorten the number, i think ultimately what you would only need for the formatting is the dollar sign, so i modified the code to add that dollar sign along with the shortened number. 

1. On your DataPage, select "Values(Y Axis)". Set the Formatting to "None" (this is vital because again, the code I have only works for those with no formatting). Do the same for X Axis as necessary.

2. On the Header, paste the following:

<script>

function intToString (num) {
if (num >= 1000000000000000) {
        return (num / 1000000000000000).toFixed(1).replace(/\.0$/, '') + 'G';
     }
if (num >= 1000000000000) {
        return (num / 1000000000000).toFixed(1).replace(/\.0$/, '') + 'T';
     }

if (num >= 1000000000) {
        return (num / 1000000000).toFixed(1).replace(/\.0$/, '') + 'B';
     }
     if (num >= 1000000) {
        return (num / 1000000).toFixed(1).replace(/\.0$/, '') + 'M';
     }
     if (num >= 1000) {
        return (num / 1000).toFixed(1).replace(/\.0$/, '') + 'K';
     }
     return num;
}

</script>

 

3. On the Footer, paste the following:

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {

var intervalId = window.setInterval(function(){
const cells = document.querySelectorAll(".highcharts-yaxis-labels text");
const amounts = document.querySelectorAll("g[class*='highcharts-label highcharts-data-label highcharts-data-label-color']  text tspan");

if (cells === null) {
} else {
clearInterval(intervalId);
[].forEach.call(cells, function(cell, i) {
var num = cell.innerHTML;
cell.innerHTML = "$" +   intToString(num);
});

[].forEach.call(amounts, function(am, i) {

am.childNodes.forEach(function(node){
  // Text nodes are nodeType: 3
  if(node.nodeType === 3 && node.nodeValue !== ""){
var num = node.nodeValue.trim()
am.parentNode.innerHTML ="$" +  intToString(num);

  }
});

});

}

}, 100);

});
</script>

 

Sample DP: https://c1hch576.caspio.com/dp/db26a000463a2944d65c4edb8ade

 

Still not getting desired result, if possible then can we connect 

 

[Rupesh]

On 7/26/2022 at 3:46 AM, futurist said:

 

Hi @Rupesh,

It seems like it's because of the formatting of your numbers to currency. I have initially set up my code to apply if no formatting is set up for the values of y axis. since we're trying to shorten the number, i think ultimately what you would only need for the formatting is the dollar sign, so i modified the code to add that dollar sign along with the shortened number. 

1. On your DataPage, select "Values(Y Axis)". Set the Formatting to "None" (this is vital because again, the code I have only works for those with no formatting). Do the same for X Axis as necessary.

2. On the Header, paste the following:

<script>

function intToString (num) {
if (num >= 1000000000000000) {
        return (num / 1000000000000000).toFixed(1).replace(/\.0$/, '') + 'G';
     }
if (num >= 1000000000000) {
        return (num / 1000000000000).toFixed(1).replace(/\.0$/, '') + 'T';
     }

if (num >= 1000000000) {
        return (num / 1000000000).toFixed(1).replace(/\.0$/, '') + 'B';
     }
     if (num >= 1000000) {
        return (num / 1000000).toFixed(1).replace(/\.0$/, '') + 'M';
     }
     if (num >= 1000) {
        return (num / 1000).toFixed(1).replace(/\.0$/, '') + 'K';
     }
     return num;
}

</script>

 

3. On the Footer, paste the following:

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {

var intervalId = window.setInterval(function(){
const cells = document.querySelectorAll(".highcharts-yaxis-labels text");
const amounts = document.querySelectorAll("g[class*='highcharts-label highcharts-data-label highcharts-data-label-color']  text tspan");

if (cells === null) {
} else {
clearInterval(intervalId);
[].forEach.call(cells, function(cell, i) {
cell.setAttribute("value", cell.innerHTML);
var num = cell.innerHTML;
cell.innerHTML = "$" +   intToString(num);
});

[].forEach.call(amounts, function(am, i) {

am.childNodes.forEach(function(node){
  // Text nodes are nodeType: 3
  if(node.nodeType === 3 && node.nodeValue !== ""){
var num = node.nodeValue.trim()
am.parentNode.innerHTML ="$" +  intToString(num);

  }
});

});

}

}, 500);

});

var intervalId = window.setInterval(function(){
const legend = document.querySelectorAll("button[aria-label*='Show']");

if (legend === null) {
} else {
clearInterval(intervalId);
[].forEach.call(legend, function(elem, i) {
legend[i].addEventListener('click', function(event) {
const cells = document.querySelectorAll(".highcharts-yaxis-labels text");
[].forEach.call(cells, function(cell, i) {
var num = cell.innerHTML;
if(num.substring(0, 1) !==  '$'){
cell.innerHTML = "$" +   intToString(num);
}
});
});
});

}

}, 100);

</script>

 

Sample DP: https://c1hch576.caspio.com/dp/db26a00081997f921d5e4eafa668

 

It is working fine but numbers are not showing on center and every time when i refresh the page then another $ sign added to the y-axis. I am using Column chart

 

[Rupesh]

On 7/26/2022 at 4:05 AM, futurist said:

<script>

function intToString (num) {
if (num >= 1000000000000000) {
        return (num / 1000000000000000).toFixed(1).replace(/\.0$/, '') + 'G';
     }
if (num >= 1000000000000) {
        return (num / 1000000000000).toFixed(1).replace(/\.0$/, '') + 'T';
     }

if (num >= 1000000000) {
        return (num / 1000000000).toFixed(1).replace(/\.0$/, '') + 'B';
     }
     if (num >= 1000000) {
        return (num / 1000000).toFixed(1).replace(/\.0$/, '') + 'M';
     }
     if (num >= 1000) {
        return (num / 1000).toFixed(1).replace(/\.0$/, '') + 'K';
     }
     if (num == '') {
        return '0';
     }
     return num;
}

</script>

Thanks @futurist, Solution is working fine however in Pivot table, it is converting the COUNT value as $ however we need only $ with K, M, B on SUM value.

In Chart, $ sign is adding twice and thrice in the Y - Axis and the lable alignment is also not on center with ever Column Chart 

[futurist]

15 hours ago, Rupesh said:

Thanks @futurist, Solution is working fine however in Pivot table, it is converting the COUNT value as $ however we need only $ with K, M, B on SUM value.

In Chart, $ sign is adding twice and thrice in the Y - Axis and the lable alignment is also not on center with ever Column Chart 

Are you saying you only need the K/M/B on the totals? If so, you can use this code for the footer:

 

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {

const cells2 = document.querySelectorAll("tr:last-child .cbResultSetTotalsDataCellNumberDate");

[].forEach.call(cells2, function(cell, i) {
var num = cell.innerHTML;
if(num.substring(0, 1) !==  '$'){
cell.innerHTML = "$" +   intToString(num);
}
});

});
</script>

[Rupesh]

5 hours ago, futurist said:

Are you saying you only need the K/M/B on the totals? If so, you can use this code for the footer:

 

<script type="text/javascript">
document.addEventListener('DataPageReady', function (event) {

const cells2 = document.querySelectorAll("tr:last-child .cbResultSetTotalsDataCellNumberDate");

[].forEach.call(cells2, function(cell, i) {
var num = cell.innerHTML;
if(num.substring(0, 1) !==  '$'){
cell.innerHTML = "$" +   intToString(num);
}
});

});
</script>

I have two column in Pivot 1. String 2. Count and 3. Sum however I need $ sign only on Column 3 along with K, M, B

[Rupesh]

In Chart, sometime K, M B is coming and sometime not and the alignment is also not proper. I have removed the $ sign because in Y axis it is coming twice or thrice. Please suggest

[futurist]

18 hours ago, Rupesh said:

I have two column in Pivot 1. String 2. Count and 3. Sum however I need $ sign only on Column 3 along with K, M, B

On the code, simply replace:

1.
const cells = document.querySelectorAll(".cbResultSetTableCellNumberDate");

with

const cells = document.querySelectorAll(" .cbResultSetTableCellNumberDate:nth-child(3)");

and 2.

const cells2 = document.querySelectorAll(".cbResultSetTotalsDataCellNumberDate");

with

const cells2 = document.querySelectorAll(".cbResultSetTotalsDataCellNumberDate:nth-child(3)");

 

and change the 3 with the order of the column it comes in. best thing to do is play around with this, add or subtract 1 until you get the desired column

 

 

[Rupesh]

On 8/4/2022 at 1:58 AM, futurist said:

On the code, simply replace:

1.
const cells = document.querySelectorAll(".cbResultSetTableCellNumberDate");

with

const cells = document.querySelectorAll(" .cbResultSetTableCellNumberDate:nth-child(3)");

and 2.

const cells2 = document.querySelectorAll(".cbResultSetTotalsDataCellNumberDate");

with

const cells2 = document.querySelectorAll(".cbResultSetTotalsDataCellNumberDate:nth-child(3)");

 

and change the 3 with the order of the column it comes in. best thing to do is play around with this, add or subtract 1 until you get the desired column

 

 

Thank you, I will try this