calculate total and average in table

[Aspiring_Developer]

Hello All,

I have below table :-

I have the values of closed_wkg_days for multiple months.

I wish to calculate the sum of all the closed _wkg_days for each month.

for example for the month of Jan my value should be the total of all the values and  then take the average for the same.

How can I achieve it ?

Thanks in advance

[Digvijay_Singh]

Your data shows you got LOB dimension also, not sure how that is needed to be used. The below expression sums all the values, month wise and then takes the average of it. You might need to update it based on your need.

Avg(Aggr(Sum(CLOSED_WKG_DAYS),Month-Year))

[aveeeeeee7en]

Hi @Aspiring_Developer,

Is this what you want:

If Yes then you need to use below expressions:

First, create a single date for every month which will help in aggregation.

See this:

MONTHNAME(DATE(DATE#(ISSUE_CLOSE_DATE,'DD-MMM-YYYY'),'DD-MM-YYYY')) AS ISSUE_CLOSE_DATE_MONTHNAME,

Expressions:

Sum Total:
SUM(TOTAL <ISSUE_CLOSE_DATE_MONTHNAME> CLOSED_WKG_DAYS)

Average:
SUM(TOTAL <ISSUE_CLOSE_DATE_MONTHNAME> CLOSED_WKG_DAYS)/COUNT(TOTAL <ISSUE_CLOSE_DATE_MONTHNAME> CLOSED_WKG_DAYS)

Regards,

Av7eN

[Aspiring_Developer]

Hi @aveeeeeee7en 

Thanks for your quick response.

I wish to show single record for each month . 

Like for Jan 2022 - total is 94 ., so there should be single row for that .

[erric3210 · Accepted solution]

Hi @Aspiring_Developer 

First add this in your Date: 

MonthName(Date(Date#(ISSUE_CLOSE_DATE,'DDD-MMM-YYYY'),'MMM-YY')) AS ISSUE_CLOSE_DATE_MONTHNAME

The above expression will first concert the Date to Number and MonthName will generate one date for several dates available in a month. eg. 5-Jan-2022, 6-Jan-2022, 7-Jan-2022, etc. all will fall under Jan 2022

Your expected output after using the new field. See this:

Hope that helps.

Regards,

Eric