Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

calculate total and average in table

Hello All,

I have below table :-

Aspiring_Developer_0-1648563988771.png

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

Labels (1)
1 Solution

Accepted Solutions
erric3210
Creator
Creator

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:

New Expected Output.PNG

Hope that helps.

Regards,

Eric

View solution in original post

4 Replies
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
Specialist III
Specialist III

Hi @Aspiring_Developer,

Is this what you want:

calculate total and average in table - Solution.PNG

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
Creator III
Creator III
Author

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

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:

New Expected Output.PNG

Hope that helps.

Regards,

Eric