Calculate total sales per individual from csv file

[BasantSingh13]

CSV has rows with the person and his sales for that hour. I want to calculate the total sales per individual from a csv file having complete day's data and send a mail highlighting the people less than a threshold sales output.

Eg. CSV

A-10

A-15

B-12

B-13

C-20

D-15

Output Mail (To show sales less than 25)

C-20

D-15

[grantjenkins]

Does your CSV file have a column for the user and another column for the hours, or is it a single column with D-10 for example?

[grantjenkins · Accepted solution]

Hopefully this is what you're looking for. I've based it on a CSV file with no column header, and single column with values C-25 for example.

For this example, I'm using the following CSV file.

See full flow below. I'll go into each of the actions.

Get file content retrieves the content of the CSV file.

Filter array converts the output to text and splits on new line character to get an array of items. It will also remove the last empty item that we always get. The expressions used are:

//From
split(base64ToString(body('Get_file_content')['$content']), decodeUriComponent('%0D%0A'))

//Condition
trim(item())

Select splits the items by '-' to get the User and the Hours. It also converts the Hours to float so we can perform calculations later. The expressions used are:

//From
@{body('Filter_array')}

//User
first(split(item(), '-'))

//Hours
last(split(item(), '-'))

XML is a Compose action that converts the Select data to XML so we can use XPath to sum the Hours for each user.

xml(json(concat('{"root": { value:', body('Select'), '}}')))

Users is a Select action that retrieves the Users. We will use this to iterate through each user later. It uses the output from the Select above and the following expression.

//From
@{body('Select')}

//Map
item()?['User']

Users Totals is a Select action that maps the User and their Total sales. Below are the expressions used. Note that we use XPath to sum the hours for each user.

//From (unique list of users)
union(body('Users'), body('Users'))

//User
item()

//Total
xpath(outputs('XML'), concat('sum(//root/value[User="', item(), '"]/Hours)'))

Users Totals Less than 25 is a Filter array action that retrieves the Users where their Total Sales is less than 25.

//From
@body('User_Totals')

//Condition
item()?['Total']

Create HTML table uses the output from Users Totals Less than 25.

Style HTML table is a Compose action that contains some CSS to make the HTML table look nicer in the email.

<style>
    table {
        border-collapse: collapse;
    }
    table td,
    table th {
        border: 1px solid #ddd;
        padding: 6px 20px;
        text-align: left;
    }
    table th {
        background-color: #1C6EA4;
        color: white;
    }
</style>

Send an email uses the output from Style HTML table and Create HTML table.

After running the flow, we would get the following email.

[BasantSingh13]

It has a separate column for both.

[grantjenkins · Accepted solution]

There's only a couple of changes we would need to make to accommodate two columns instead of one.

Assuming we had a CSV file as shown below.

Only the Filter array and Select would need to be modified. Everything else would be as per the above solution.

Filter array would use the following expressions. Effectively, skipping the header row so we only get the data.

//From
skip(split(base64ToString(body('Get_file_content')['$content']), decodeUriComponent('%0D%0A')), 1)

//Condition
trim(item())

Select would use the following expressions. Splitting on comma instead of hyphen.

//User
first(split(item(), ','))

//Hours
float(last(split(item(), ',')))

[BasantSingh13]

@grantjenkins . Getting the below Error on Users_Total.

The execution of template action 'Users_Total' failed: The evaluation of 'query' action 'where' expression '{
"User": "@item()",
"Total": "@xpath(outputs('XML'), concat('sum(//root/value[User=\"', item(), '\"]/Hours)'))"
}' failed: 'The template language function 'xpath' parameters are invalid: the 'xpath' parameter must be a supported, well formed XPath expression. Please see https://aka.ms/logicexpressions#xpath for usage details.'.

Also would there be any changes required if the User column has First_Name<Space>Last_Name? Appreciate your help.

[BasantSingh13]

Kindly ignore. The flow worked. Thanks again for you quick efforts.😃😀

[BasantSingh13]

Also can we have the results in ascending order with totals divided by 60? Thanks a lot!

[grantjenkins · Accepted solution]

You would just need to change the Create HTML table to have it sorted by Total in ascending order and Totals divided by 60. See below.

//From (sorted by Total in ascending order)
sort(body('Users_Totals_Less_than_25'), 'Total')

//User
item()?['User']

//Total (divided by 60 with 2 decimal places)
formatNumber(div(item()?['Total'], 60), 'N2')

[JJarvelainen19]

I have a related issue, but perhaps simpler. I just want to calculate the sum (something like the Total from the 'User Totals'), and update a row in excel.

The update excel part is familiar to me, but how do I get the value for Total in a number format that can be used to update a cell in Excel?

I tried small modifications according to the picture, with everything up to 'Users' the same.