topic Re: calculate total and average in table in App Development

calculate total and average in table

Aspiring_Developer — Tue, 29 Mar 2022 14:40:52 GMT

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

Re: calculate total and average in table

Digvijay_Singh — Tue, 29 Mar 2022 14:49:15 GMT

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

Re: calculate total and average in table

aveeeeeee7en — Tue, 29 Mar 2022 16:10:52 GMT

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

Re: calculate total and average in table

Aspiring_Developer — Wed, 30 Mar 2022 08:26:22 GMT

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 .

Re: calculate total and average in table

erric3210 — Wed, 06 Apr 2022 19:29:53 GMT

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