Grabbing the value and calculate "with Respect to Date"


From the above Data table’s screenshot, My query is -
1)I need the Balance Column values with respect to date, For example - For January, it needs to collect all values of Jan month available in excel and divide it by Total number of January values the bot got, In the other words “Average for each month”. Like wise for every month’s data the table have.!! @indrajit.shah @Palaniyappan @NIVED_NAMBIAR Your thoughts on this query also ll help !!

Thanks in advance !!

Input.xlsx (8.6 KB)
This is the input file and in this input file i have Just two columns which we need to proceed the calculation, But in the actual case, there might be many columns in the input file, but “Date” And “Balance” Columns are unique sir.

I have attached the output excel file where it ll have each month’s Average balance that are calculated from the input file.
Output.xlsx (8.4 KB)

Hi @Rakesh_Sampath check this workflow (261.3 KB)

Hope it helps you


Nived N

Happy Automation

1 Like

This solution is working when there is just two columns as in the input file !!

But in the other case there might be many columns in the input, but the Date and Balance column will be unique bro !! @NIVED_NAMBIAR , I checked with this scenario , Its not even getting in to the “If” loop !! :frowning:

What condition u put in filter activity ?

In column tab i gave “Date” ,Operation is Contains , Value is item as you mentioned @NIVED_NAMBIAR

Hi @Rakesh_Sampath

Can u show read range property panel ?

Did u tick the Preserve Format option

Yeah sure sir

May be filter operation may not be working

Can u share the screenshot of the filter datatable and workflow

1 Like

Did u specified the item datatype as object ?

1 Like

Yes sir !! I did

Find starter help here:
GroupBy_1ColDate_Avg.xaml (7.2 KB)

Doing it with following LINQ:

(From d In dtData.AsEnumerable
Group d By k1=DateTime.ParseExact(d("Date").toString.Trim,"dd-MMM-yy", CultureInfo.InvariantCulture).toString("MMM-yyyy") Into grp=Group
Let avg = grp.Average(Function (x) CDbl(x("Balance").toString.Trim))
Let ra = New Object(){k1.Substring(0,3),avg}
Select dtResult.Rows.Add(ra)).CopyToDataTable 

@indrajit.shah and @NIVED_NAMBIAR will help you to finalize it, as I am travelling and bad conntected to the internet