topic Re: Find peak date with highest transactions and expression with average for entire week. in QlikView

Find peak date with highest transactions and expression with average for entire week.

mp802377 — Mon, 07 Aug 2017 22:04:17 GMT

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.

Re: Find peak date with highest transactions and expression with average for entire week.

sunny_talwar — Tue, 08 Aug 2017 00:49:41 GMT

Would you be able to share some raw data and explain as to what exactly the output needs to look like (numerically)

Re: Find peak date with highest transactions and expression with average for entire week.

mp802377 — Tue, 08 Aug 2017 04:36:46 GMT

Hello,

I attached an excel spreadsheet of how I need it to look. The main fields would be:

Date

Count

Mean Time

The other was added so you could see what data I need for the chart.

Thank you for the help!

Martha

Re: Find peak date with highest transactions and expression with average for entire week.

tresB — Tue, 08 Aug 2017 05:11:17 GMT

May be this?

Script:

LOAD

Date(Date) as Date,

Week(WeekStart(Date,0,4)) as Week,

Count,

[Mean Time]

FROM

(ooxml, embedded labels, table is Sheet1);

Dimension: =Date(Aggr(FirstSortedValue(Date, -Count), Week))

Expression: =Avg([Mean Time])

Re: Find peak date with highest transactions and expression with average for entire week.

Kushal_Chawda — Tue, 08 Aug 2017 10:00:57 GMT

Back end solution

Data:

LOAD

Weekday,

Date,

WeekEnd(Date, 0,4) as WeekEnd,

Count,

num([Mean Time]) as [Mean Time]

FROM

[example peak transaction day.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD WeekEnd,

Avg([Mean Time]) as [Avg Mean]

Resident Data

Group by WeekEnd;

Dimension:

WeekEnd

Expression:

=only([Avg Mean])

Front end solution

Data:

LOAD

Weekday,

Date,

Count,

num([Mean Time]) as [Mean Time]

FROM

[example peak transaction day.xlsx]

(ooxml, embedded labels, table is Sheet1);

Dimension:

WeekEnd(Date, 0,4)

Expression:

=avg([Mean Time])

Re: Find peak date with highest transactions and expression with average for entire week.

mp802377 — Thu, 10 Aug 2017 14:27:15 GMT

Hello,

Thank you for the post, but it is not pulling in the peak transaction dates. There are a few dates that match, but when I did it, there were many dates that are not the peak.

Thank you

Martha

Re: Find peak date with highest transactions and expression with average for entire week.

mp802377 — Thu, 10 Aug 2017 14:30:20 GMT

Hello,

This is working but how do I get it to go back farther than the previous 4-5 weeks? I need it to go back a year.

Thank you for this!

Martha

Re: Find peak date with highest transactions and expression with average for entire week.

tresB — Fri, 11 Aug 2017 10:29:37 GMT

Could you please explain a bit more?

Re: Find peak date with highest transactions and expression with average for entire week.

Kushal_Chawda — Fri, 11 Aug 2017 13:25:29 GMT

what do you mean by peak transaction? as per the excel file attached, I have created same line chart. Can you please tell me what is missing?

Re: Find peak date with highest transactions and expression with average for entire week.

mp802377 — Fri, 11 Aug 2017 13:42:19 GMT

The peak transaction dates are the dates with the highest counts. I don't know if I explained that well. While most of the peaks are the last day of the week, true, but not all. So the peak dates on the spreadsheet are:

Date with Peak Transaction

1/12/2017

1/18/2017

1/26/2017

2/2/2017

2/4/2017

2/10/2017

2/20/2017

2/26/2017

3/6/2017

3/15/2017

3/19/2017

3/25/2017

4/3/2017

4/7/2017

4/17/2017

4/26/2017

5/3/2017

and in the Qlikview app you created there is

1/13/2017

1/20/2017

1/27/2017

2/3/2017

2/10/2017

2/17/2017

2/24/2017

3/3/2017

3/10/2017

3/17/2017

3/24/2017

3/31/2017

4/7/2017

4/14/2017

4/21/2017

4/28/2017

5/5/2017

I apologize if I didn't explain better. The peak transaction would be the date with the peak count.

Martha

Re: Find peak date with highest transactions and expression with average for entire week.

mp802377 — Fri, 11 Aug 2017 14:05:05 GMT

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?

Thank you,

Martha Parsons