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