# App Development

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Creator III

## calculate total and average in table

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 ?

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Creator

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

4 Replies

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

Specialist III

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

Creator III
Author

I wish to show single record for each month .

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

Creator

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

Tags
Community Browser