topic Re: Calculate the attendance based on a monthly basis in a Pivot Table in App Development

Calculate the attendance based on a monthly basis in a Pivot Table

andymanu — Sat, 16 Nov 2024 21:24:19 GMT

Hi All,

I need to calculate the percentage of attendees for each month by the total number of participants for the month.

(Total number of participants comprise of both "Attended" people and the "Did Not Attend" people).

Please refer the provided screenshot and the data set herewith.

For example, the respective percentage under month "December" for the Age Group "Age 0-19" should be 42.5% which is 17 / 17+5+15+3 (Attended number for the age group divided by the total number of participants for the selected month, which is "December".

Similarly, Percentage for the month "January" for the same group (Age 0-19) should be 21% ( 12/56).

I tried an expression as "=Num(Sum(Attended) / Sum(TOTAL <[Month]>Total_Attendance),'#,##0%')" to calculate the percentage (as per the calculated screen shot data) of attendance but does not give me the expected outcome.

Could someone please help me to derive the correct expression to calculate the correct percentage based on a monthly basis.

Thank you in advance.

Kind regards,

Andy

Re: Calculate the attendance based on a monthly basis in a Pivot Table

andymanu — Fri, 15 Feb 2019 05:13:40 GMT

Hi All,

I was able to develop an expression to perform the above task, but it does the job partially.

The expression I developed is, "=Num(Sum(Attended) / Sum(Aggr(Sum(Total_Attendance),Month,Company)),'#,##0%')".

The above expression calculates the percentage value only for the first Age group range of each month but not for the rest.

When analysed bit deeper, found that the "Total Participants" were calculated only for the first age range of each month but not for the remainder.

Please see the attached screenshot herewith.

Appreciate a lot if someone could help me to resolve the said issue.

Thank you in advance.

Kind regards,

Andy

Re: Calculate the attendance based on a monthly basis in a Pivot Table

andymanu — Fri, 15 Feb 2019 05:15:13 GMT

Just a small not,
I have created a dimension during the data load process named, "Total_Attendance" which adds both "Attended" people and the "Did Not Attend" people.
Thank you.
Regards,
Andy

Re: Calculate the attendance based on a monthly basis in a Pivot Table

sunny_talwar — Fri, 15 Feb 2019 13:21:34 GMT

Try this

=Num(Sum(Attended) / Sum(TOTAL <[Month], [Company]> Total_Attendance), '#,##0%')

=Num(Sum(Attended) / Sum(Aggr(NODISTINCT Sum(Total_Attendance), Month, Company)), '#,##0%')

Re: Calculate the attendance based on a monthly basis in a Pivot Table

andymanu — Sat, 16 Feb 2019 00:55:39 GMT

Hi MVP,
You are a legend.
Thank you very much for your prompt reply.
The first options gives the expected output. I think the modifier (with in "< >" should contain both Month and Company.
Jus t a small request,
Appreciate if you could give your comments on the question posted under the below link,
https://community.qlik.com/t5/Qlik-Support-Discussions/Represent-the-percentage-of-Attendance-ONLY-for-the-selected/m-p/1543307#M1168

Kind regards,
Andy