Find peak date with highest transactions and expression with average for entire week.
Hello,
I have this set of data. On the chart for the dates going across the X-Axis, they want to see the peak date that had the highest transactions for the week. Then in the expressions, they want to see the average time in seconds for the entire week.
I had originally had in the dimensions this:
=Date(WeekStart(Date, 0,4),'DD-MMM-YY')
And in the expressions, I had this:
Avg({<Year = {$(varYear)} >}seconds)
I thought that was great until they told me the needed the date on the x-axis to show the date of that week with the highest transactions. If I could figure out how to get the dimensions to show the peak date (where the transactions were at the max), how would I show an average for the entire week? Is this possible in QlikView? Please say yes.
I first thought a resident table somehow in the load script? But even if I got that right, how would I get the average for the entire week.
The fields are broken down below.
Date,
count,
mean
Then, of course, I have my calendar setup with Weekstart, Date, Month, MonthYear, Year.....
Thank you so much for any help!
I put this under development because I think it will involve the editor, but this could also go in scripting and visualization.
It is adding a - to the firstsortedvalue field. Below I am just using count because I wanted to verify it was grabbing the highest count. I will be doing an average (mean).
=Date(Aggr(FirstSortedValue(Date, -count), Week))
sum(count)
7/11/2017
8408524
7/17/2017
8640739
7/25/2017
8749096
8/1/2017
8576821
-
666029837
You can see there is data there for the dates.
-
27-May-17
737151
-
13-May-17
777239
-
26-May-17
848803
-
20-May-17
799815
-
28-May-17
847770
-
03-Jun-17
861716
-
10-Jun-17
884653
-
14-May-17
874627
-
18-May-17
887556
-
12-May-17
915870
-
19-May-17
916466
but for some reason, it is giving a -. I added distinct (because I read that yesterday if you get a NULL value, but that didn't do anything, so I took it off. Why does it do that?
But the charts looked great, it is just that they only had 5 weeks. Is there a way to fix that?
