Sum Column in DataTable based on Regions

Help Studio
1

I have an excel from where I have to

  1. Calculates the total sales for each region(central, east, west)
  2. Displays, in a message box, the region with the most sales and the region with the least sales, with the corresponding sales amounts
2

Hi @Dimple_Mandal

Share the sample excel file.

1 Like
4

@Dimple_Mandal

Check the xaml attached

Sales Count.xaml (8.5 KB)

5

Total sales Calculation is not coming correct and no msg box is getting displayed with most and least sales.
|

6

Hi @Dimple_Mandal ,

You can check below video to do group by on region get the result.

1 Like
7

Hi @Dimple_Mandal

For find the total sales please try this,

Create a datatable using build datatable with two columns region and total,

The Outdttable will contains the regions total sales ,write this excel and test it.

Outdttable = (From row in dtTable.asenumerable
Group row by k = row("Region").tostring into grp = group
Let sum = grp.sum(function(x) Cdbl(x("Total").tostring))
Let ra = new object() {grp(0)("Region"),sum}
select Outdttable.rows.add(ra)).copytodatatable

Now for find the max and min values put this in message box,

For max value ,

Outdttable.select("[Total] = '" + 
Outdttable.asenumerable.max(function(row) cdbl(row("Total"))).tostring + "'")(0)("Region").tostring

For min value ,

Outdttable.select("[Total] = '" + 
Outdttable.asenumerable.min(function(row) cdbl(row("Total"))).tostring + "'")(0)("Region").tostring

Didn’t test this please test and let me know if anything required.

Thanks

2 Likes
8

Thankyou,i will try and will surely let you know

9

@Dimple_Mandal

Check the updated xaml file

Sales Count.xaml (9.0 KB)

10

@Dimple_Mandal Attached the workflow tested it please use this,

Main.xaml (11.4 KB)

Thanks

3 Likes
11

@prasath_S: Yes, This one worked like wonder.Thank you so much

12

Happy to hear @Dimple_Mandal Please mark it is solution and close the thread, so it will helpful to others when they search the same query…

Thanks

13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.