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

[mp802377]

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.

[sunny_talwar]

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

[mp802377]

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

[tresesco]

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])

[Kushal_Chawda]

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])

[mp802377]

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

[mp802377]

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

[tresesco]

Could you please explain a bit more?

[Kushal_Chawda]

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?

[mp802377]

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