Skip to main content

App Development

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

Announcements
JAN. 24, Q&A with Qlik: Making the Move to Qlik Cloud Reporting: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jlampard40
Contributor III
Contributor III

Max Min and Average Per Day Count

Hi - I have a formula for set analysis which calculates my average count per day as follows:

Num(avg({<Activity_or_Demand={'Demand'},Room-={'CTSIM','CTSIM2'}, Calendar_Year_Month={'2022-04'},Individual_Exam_Parts_Per_Event-={'CLDTH'},AD_Day-={'Saturday','Sunday'}>}
Aggr(count({<Activity_or_Demand={'Demand'},Room-={'CTSIM','CTSIM2'},Calendar_Year_Month={'2022-04'},Individual_Exam_Parts_Per_Event-={'CLDTH'},AD_Day-={'Saturday','Sunday'}>}
Event_Key),Request_Date_Day)),'#,###.')

This plots beautifully on a chart, using my dimension 'Calendar_Month' - so I am essentially displaying average count of an exam per day, over the course of a whole month.  However, I would like to also show the Maximum and Minimum average per day also, for each month.  How can I modify the above formula to show max / min on the same bar?  Many thanks!

Labels (1)
2 Replies
vinieme12
Champion III
Champion III

Calculate AVG manually 

=Num(count({<Activity_or_Demand={'Demand'},Room-={'CTSIM','CTSIM2'},Calendar_Year_Month={'2022-04'},Individual_Exam_Parts_Per_Event-={'CLDTH'},AD_Day-={'Saturday','Sunday'}>}

Event_Key)

/

count({<Activity_or_Demand={'Demand'},Room-={'CTSIM','CTSIM2'},Calendar_Year_Month={'2022-04'},Individual_Exam_Parts_Per_Event-={'CLDTH'},AD_Day-={'Saturday','Sunday'}>}

Request_date_day)

,'#,###.')

 

Then Max

=Num(Max(Aggr(count({<Activity_or_Demand={'Demand'},Room-={'CTSIM','CTSIM2'},Calendar_Year_Month={'2022-04'},Individual_Exam_Parts_Per_Event-={'CLDTH'},AD_Day-={'Saturday','Sunday'}>}

Event_Key)

/

count({<Activity_or_Demand={'Demand'},Room-={'CTSIM','CTSIM2'},Calendar_Year_Month={'2022-04'},Individual_Exam_Parts_Per_Event-={'CLDTH'},AD_Day-={'Saturday','Sunday'}>}

Request_date_day)

,Request_date_day,MonthDimension))

,'#,###.')

 

 

 

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
jlampard40
Contributor III
Contributor III
Author

@vinieme12  - thanks for your reply, but this doesn't give me what I need to show on my chart.

I perhaps need three elements in each 'bar' of the chart (per calendar month) to show the average count per day (over the course of the month), then an element in the same bar to show the max count which has occurred, and the min count which has occurred - all throughout the calendar month.

I've attached a visual example using Excel to try to demo what I mean - ideally all the elements (avg per day, max and min) would be represented on each column, per month.

Look forward to your reply - many thanks!