how to calculate the average in bar chart

konidena — Sat, 16 Nov 2024 07:06:09 GMT

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

Re: how to calculate the average in bar chart

Gysbert_Wassenaar — Fri, 07 Dec 2018 13:43:42 GMT

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

Re: how to calculate the average in bar chart

konidena — Fri, 07 Dec 2018 13:53:30 GMT

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

Re: how to calculate the average in bar chart

Gysbert_Wassenaar — Fri, 07 Dec 2018 17:00:49 GMT

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

topic Re: how to calculate the average in bar chart in App Development

how to calculate the average in bar chart

Re: how to calculate the average in bar chart

Re: how to calculate the average in bar chart

Re: how to calculate the average in bar chart