Skip to main content
J
Janicen16-Pearl
16-Pearl
August 14, 2023
Solved

SUM of Total sales per day

  • August 14, 2023
  • 1 reply
  • 1000 views

Good day Community,

 

I have an Info table that stores the sales of fruit a day. See (MyTable.png)

EXCEL_FzaDE0JThp.png<MyTable>

 

I want to add the number of fruits I sold each day then reorganize my table to that the day that has the highest sales can appear at the top of my list, and the day I on which I had the least sales should appear at the bottom of the info table. See the image below:

 

EXCEL_6XyiUBkVBq.png

I've written up a piece of code, however I have hit a wall because the sum of the sales per day is not correct. How can I fix this:

let Total_Sales_Per_Day = 0;
for(let c=0; c<Fruit_Names.length; c++){//the column names from the input
Fruit_Input = Fruit_Names[c].Fruit_Names;

Value = MyTable[Fruit_Input];
Value = parseFloat(Value);
let Total_Sales_Per_Day = Total_Sales_Per_Day + Value;
}
let result = Total_Sales_Per_Day

 

Regards,

 

 

Best answer by Velkumar

Hi @Janicen 

 

Please try below code

 

 

// result: INFOTABLE dataShape: ""
let data = me.GetDataTableEntries({
	maxItems: 10 /* NUMBER {"defaultValue":500} */
});

// Add Total field to infotable to store total value
let newField = {
 name: "Total",
 baseType: 'INTEGER'
};
data.AddField(newField);

// Iterate over infotable data
for(var i = 0; i < data.length; i++)
{
 // Add Orange and Apple Count
 var total = data.rows[i].Orange + data.rows[i].Apple;
 // Update Total value to infotable
 data.rows[i].Total = total;
}

// Sort infotable based on highest total
let params = {
	sortColumn: "Total" /* STRING */,
	t: data /* INFOTABLE */,
	ascending: false /* BOOLEAN {"defaultValue":true} */
};

// result: INFOTABLE
let result = Resources["InfoTableFunctions"].Sort(params);

// To get the highest sale day 
// let result = data.Day // Output will be STRING and rename sort 'result' to 'data'

 

The output will look like this:

Velkumar_0-1692000870552.png

 

Or if you want only the highest sale day

 

// result: INFOTABLE dataShape: ""
let data = me.GetDataTableEntries({
	maxItems: 10 /* NUMBER {"defaultValue":500} */
});

/************************************************/

var hightestTotal = 0;
var hightestSaleDay = '';

// Iterate over infotable data
for(var i = 0; i < data.length; i++)
{
 // Add Orange and Apple Count
 var total = data.rows[i].Orange + data.rows[i].Apple;
	if(total > hightestTotal)
 {
 hightestSaleDay = data.rows[i].Day;
 hightestTotal = total;
 }
}

var result = hightestSaleDay;

 

/VR

 

 

1 reply

V
19-Tanzanite
Velkumar19-TanzaniteAnswer
19-Tanzanite
August 14, 2023

Hi @Janicen 

 

Please try below code

 

 

// result: INFOTABLE dataShape: ""
let data = me.GetDataTableEntries({
	maxItems: 10 /* NUMBER {"defaultValue":500} */
});

// Add Total field to infotable to store total value
let newField = {
 name: "Total",
 baseType: 'INTEGER'
};
data.AddField(newField);

// Iterate over infotable data
for(var i = 0; i < data.length; i++)
{
 // Add Orange and Apple Count
 var total = data.rows[i].Orange + data.rows[i].Apple;
 // Update Total value to infotable
 data.rows[i].Total = total;
}

// Sort infotable based on highest total
let params = {
	sortColumn: "Total" /* STRING */,
	t: data /* INFOTABLE */,
	ascending: false /* BOOLEAN {"defaultValue":true} */
};

// result: INFOTABLE
let result = Resources["InfoTableFunctions"].Sort(params);

// To get the highest sale day 
// let result = data.Day // Output will be STRING and rename sort 'result' to 'data'

 

The output will look like this:

Velkumar_0-1692000870552.png

 

Or if you want only the highest sale day

 

// result: INFOTABLE dataShape: ""
let data = me.GetDataTableEntries({
	maxItems: 10 /* NUMBER {"defaultValue":500} */
});

/************************************************/

var hightestTotal = 0;
var hightestSaleDay = '';

// Iterate over infotable data
for(var i = 0; i < data.length; i++)
{
 // Add Orange and Apple Count
 var total = data.rows[i].Orange + data.rows[i].Apple;
	if(total > hightestTotal)
 {
 hightestSaleDay = data.rows[i].Day;
 hightestTotal = total;
 }
}

var result = hightestSaleDay;

 

/VR

 

 

    Terms & ConditionsAccessibility statement