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
