Qlik Community

Qlik Sense App Development

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

konidena
Contributor

how to calculate the average in bar chart

Hi Team,

I have the block point related to the implementation of Average Line in Qlik Sense Bar Chart.

Here are the details. 

 

Dimension: Weekend- Date (It is Drill down dimension)

Weekend is 1st level

Date is second level . 

Note: For my client, the weekend is Tuesday. So i used Weekend (date, 0,2) to calculate the Weekend of every date

Measure: Sum(TimeWorked)

 

Requirement: 

1. I have to show the last 4 weeks data in the bar chart. So, bar chart shows 4 bars (4 Weekends) by default. 

    This time, average should be  Sum(time worked)/4 because it is 4 bars

2. If user select any Weekend, then the bar chart should go to next level and shows 7 days in a week. This time, average is 

    Sum(time Worked)/7

 

Issue: 

This two scenarios are working fine. but, when user select two Weekends, or two dates from different weeks the average is not showing correct. Please let me know if anyone implemented this scenario.

 

Here the filters for date is Year, Monthyear( Aug-2018), Weekend, Date

 

apart from these 4 filters, there are someother filters. But the issue is , if user select filters not related to date then the expression changing its values as per the applicable dates. User is not sure about what dates it select by selecting the filter. Also, Average should calculate  as per the number of weekend bars displayed in chart

 

=If(GetSelectedCount(PayWeekEnd)=0 and GetSelectedCount(PayrollRecordDate)=0 and GetSelectedCount(MonthYear)=0 and GetSelectedCount(Year)=0

,sum({<PayrollRecordDate={">=$(=Date(WeekStart(max(PayWeekEnd)-21,0,2),'MM/DD/YYYY')) <=$(=Date(max(PayWeekEnd),'MM/DD/YYYY'))"}>}TimeWorked)/4,

if(GetSelectedCount(PayWeekEnd)>0 and GetSelectedCount(PayrollRecordDate)=0,
Sum({<PayrollRecordDate={">=$(=Date(WeekStart(Max(PayWeekEnd),0,2),'MM/DD/YYYY')) <=$(=Date(max(PayWeekEnd),'MM/DD/YYYY'))"}>}TimeWorked)/7,
Avg(Aggr(Sum(TimeWorked),PayrollRecordDate))
))

Regards

Srinivas

Labels (2)
3 Replies

Re: how to calculate the average in bar chart

Perhaps you can multiply the 4 and 7 by the distinct number of weeks in the selection.

talk is cheap, supply exceeds demand
konidena
Contributor

Re: how to calculate the average in bar chart

I didn't understand clearly. Could you please explain more.

Re: how to calculate the average in bar chart

Can you post a small QS app that I can use to show you what to change?

talk is cheap, supply exceeds demand
Community Browser