Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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

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

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted

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

View solution in original post

4 Replies
Highlighted
Creator II
Creator II

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

Highlighted
Creator II
Creator II

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
Highlighted

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

View solution in original post

Highlighted
Creator II
Creator II

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