Grabbing the value and calculate "with Respect to Date"

Help
, ,
#1

image

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 !!

#2

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)

#3

Hi @Rakesh_Sampath check this workflow
DataScrapping1.zip (261.3 KB)

Hope it helps you

Regards

Nived N

Happy Automation

1 Like
#4

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:

#5

What condition u put in filter activity ?

#6

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

#7

Hi @Rakesh_Sampath

Can u show read range property panel ?

Did u tick the Preserve Format option

#8

Yeah sure sir

image
image267×514 7.57 KB

#9

May be filter operation may not be working

Can u share the screenshot of the filter datatable and workflow

1 Like
#10

image
image1012×475 17.4 KB

#11

Did u specified the item datatype as object ?

1 Like
#12

Yes sir !! I did

#13

@Rakesh_Sampath
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

3 Likes