Jump to content

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


Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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>

Link to comment
Share on other sites

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>


 

Link to comment
Share on other sites

 

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

image.png.1fbe96cb9c30e149d9ca9b5da3d356c0.png

image.png.6977e236bbfdbbe163d3bb8bd1c222d7.png

image.thumb.png.38a55bc7ea9d2468d32b959d9e0fa7de.png

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

 

Link to comment
Share on other sites

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>

image.thumb.png.4aeb5084e85cfae53ab6be0761802280.png

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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>

Link to comment
Share on other sites

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

IMG_20220803_070757.jpg

Link to comment
Share on other sites

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

IMG_20220803_070757.jpg

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

image.thumb.png.026bf6335269799b5d9a71d49b0100b2.png

 

 

Link to comment
Share on other sites

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

image.thumb.png.026bf6335269799b5d9a71d49b0100b2.png

 

 

Thank you, I will try this

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...