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

[andymanu]

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

[andymanu]

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

[andymanu]

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

[sunny_talwar · Accepted solution]

Try this

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

or

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

[andymanu]

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-f...

Kind regards,
Andy