CODEWITHSUNDEEP
javascripthtmljqueryjson
user3386779
user3386779

Reputation: 7175

get the sum of the json value

I have json value and I found the matching of specific column geo and salessale.Along with that I want to receive the sum of pipelinevalue .I want to finc the sum of pipeline value for 'Proposal Submitted' and 'close won' as tryFind.count .Now I got the value o

const data ={
   "pipeline":{
      "data":[
         {
            "UserName":"Jaison Clinton",
            "Geo":"NSU",
            "SalesStage":"Proposal Submitted",
            "PipelineValue":"3,54,000"
            
         },
         {
            "UserName":"Jaison Clinton",
            "Geo":"NSU",
            "SalesStage":"Proposal Submitted",
            "PipelineValue":"3,49,000"
         },
         {
            "UserName":"Kaviarasan Selvaraj",
            "Geo":"NSU",
            "SalesStage":"Proposal Submitted",
            "PipelineValue":"70,000"
         },
         {
            "UserName":"Vivek Sharma",
            "Geo":"NSU",
            "SalesStage":"close won",
            "PipelineValue":"13,45,000"
            
         }
      ]
   }
};
const result = data.pipeline.data.reduce((acc, lead) => {
    const tryFind = acc.find(l => l.Geo === lead.Geo && l.SalesStage === lead.SalesStage);
var PipelineValue=0;
    if (tryFind) {
        tryFind.count++;
        PipelineValue=PipelineValue+lead.PipelineValue;
    } else {
        acc.push({ Geo: lead.Geo, SalesStage: lead.SalesStage, count: 1 ,PipelineValue:PipelineValue});
    }

    return acc;
}, []);

console.log(result);

want popeline value as 773,000 (sum of nsu geo 'proposal submitted')and 13,45,000(sum of nsu geo close won)

Upvotes: 0

Views: 93

Answers (3)

Scott Sauyet
Scott Sauyet

Reputation: 50787

I might write it like this:

const extract = data => Object.values (
  data .reduce ((a, {Geo, SalesStage, PipelineValue}) => {
    const key = Geo + '~' + SalesStage
    return {...a, [key]: {
      Geo,
      SalesStage,
      count: (((a[key] || {}).count) || 0) + 1,
      PipelineValue: ((a[key] || {}).PipelineValue || 0) + 
                     Number(PipelineValue.replace(/,/g, ''))
    }}
  }, {})
)

const data = {pipeline: {data: [{UserName: "Jaison Clinton", Geo: "NSU", SalesStage: "Proposal Submitted", PipelineValue: "3,54,000"}, {UserName: "Jaison Clinton", Geo: "NSU", SalesStage: "Proposal Submitted", PipelineValue: "3,49,000"}, {UserName: "Kaviarasan Selvaraj", Geo: "NSU", SalesStage: "Proposal Submitted", PipelineValue: "70,000"}, {UserName: "Vivek Sharma", Geo: "NSU", SalesStage: "close won", PipelineValue: "13,45,000"}]}}


console .log (extract (data .pipeline .data))
.as-console-wrapper {min-height: 100% !important; top: 0}

We don't wrap your numeric results back up with commas. That wouldn't be a hard thing to add if necessary.

Upvotes: 1

Carsten Massmann
Carsten Massmann

Reputation: 28196

The whole thing in two lines:

const result = data.pipeline.data.reduce((acc,lead)=> 
               (acc+=+lead.PipelineValue.replace(/,/g,''),acc), 0);

OK, edited, now this is the summation for the different Geos and SalesStages:

const data ={
   "pipeline":{
      "data":[
         {
            "UserName":"Jaison Clinton",
            "Geo":"NSU",
            "SalesStage":"Proposal Submitted",
            "PipelineValue":"3,54,000"
            
         },
         {
            "UserName":"Jaison Clinton",
            "Geo":"NSU",
            "SalesStage":"Proposal Submitted",
            "PipelineValue":"3,49,000"
         },
         {
            "UserName":"Kaviarasan Selvaraj",
            "Geo":"NSU",
            "SalesStage":"Proposal Submitted",
            "PipelineValue":"70,000"
         },
         {
            "UserName":"Vivek Sharma",
            "Geo":"NSU",
            "SalesStage":"close won",
            "PipelineValue":"13,45,000"
            
         }
      ]
   }
};
const result = Object.entries(data.pipeline.data.reduce((acc,cur) => { 
      let k=cur.Geo+'|'+cur.SalesStage;
      (acc[k]=acc[k] || []).push(+cur.PipelineValue.replace(/,/g,''))
      return acc}, {}))
    .map(e=>{
      let k=e[0].split('|'); 
      return {Geo:k[0],SalesStage:k[1],count:e[1].length,PipelineValue:e[1].reduce((a,c)=>a+c)} 
     });
console.log(result);

I first collect individual arrays with PipelineValues and in the last .reduce() part I get their length and sum them up.

Upvotes: 2

Adam Coster
Adam Coster

Reputation: 1462

If you remove the commas in your PipelineValue values, you'll be able to cast those strings to numbers. If you can't change the format in the source, you can do it easily in your script:

// Original line
PipelineValue=PipelineValue+lead.PipelineValue;

// Replace commas with nothing, then cast as number (using unary +)
PipelineValue=PipelineValue+(+lead.PipelineValue.replace(/,/g,''));

Upvotes: 1

Related Questions