topic daily data to weekly in QlikView

daily data to weekly

msmichael — Wed, 25 Nov 2020 16:16:04 GMT

Hi experts,

I have three fields in my qvw, some of data looks like below,

reportId	reportDate	pageCount
1	4/30/2019	100
1	5/1/2019	102
1	5/2/2019	110
1	5/6/2019	130
...	...	...

for each reportId, I have many reportDate (not necessarily for everyday, some dates could be missing), on each reportDate, I have a pageCount number.

I want to get a weekly stat. So, for each week, I need to get the most recent (closest reportDate to the week end date) pageCount for each reportId, then I sum up the pageCount to get the total number for the week.

so, in the attached qvw file, I should get below result,

week	Total
18	330
19	475
20	531
21	600

The expression I am using only gives me the last week number..

How can I do it?

The qvw file is attached.

Thanks,

Michael

Re: daily data to weekly

msmichael — Fri, 24 May 2019 19:00:17 GMT

to clarify, if I have data in below table,

reportId	reportDate	pageCount
1	04/30/2019	100
1	05/01/2019	105
1	05/02/2019	110
1	05/06/2019	130
1	05/07/2019	135
1	05/08/2019	140
1	05/09/2019	145
1	05/13/2019	165
1	05/14/2019	170
1	05/15/2019	175
1	05/16/2019	180
1	05/20/2019	200
2	04/30/2019	100
2	05/02/2019	110
2	05/06/2019	130
2	05/07/2019	135
2	05/08/2019	140
2	05/09/2019	145
2	05/10/2019	150
2	05/13/2019	165
2	05/14/2019	170
2	05/15/2019	190
2	05/16/2019	170
2	05/20/2019	200
3	04/30/2019	100
3	05/01/2019	105
3	05/02/2019	110
3	05/06/2019	130
3	05/07/2019	135
3	05/08/2019	140
3	05/09/2019	165
3	05/10/2019	180
3	05/13/2019	165
3	05/14/2019	170
3	05/15/2019	175
3	05/16/2019	181
3	05/20/2019	200

First step is to get the latest pageCount for each reportId and each week, below is the result I expect, (also highlighted in the above table in red)

reportId	week	pageCount
1	18	110
1	19	145
1	20	180
1	21	200
2	18	110
2	19	150
2	20	170
2	21	200
3	18	110
3	19	180
3	20	181
3	21	200

then I need to sum up the pageCount for each week, then I get this as the final result,

week	Total
18	330
19	475
20	531
21	600

Re: daily data to weekly

Vegar — Fri, 24 May 2019 20:32:42 GMT

To get highest pagecount per reportId and Week in dimension

=FirstSortedValue (pageCount, -pageCount)

The sum of highest pagecount per reportid and week (not dependent on the dimension in chart)

=sum(aggr(max(pageCount), Week,reportId))

Re: daily data to weekly

msmichael — Tue, 28 May 2019 14:48:07 GMT

Hi Vegar,

Thank you for your reply.

Actually I don't want to get the "highest" pagecount per reportId and Week, I want to get the "latest" pagecount per reportId and Week. For example, for reportId=1, if there's a pagecount number on Friday in a week, I'll use this number, if not, I'll use the Thursday's pagecount number, and if there's no Thursday number, I'll use Wednesday's, and so on..

After I get the "latest" pagecount for each reportId in each Week, I need to sum them up grouping by Week, so I get the total pagecount of all reports for each week..

How do I do this?

Thanks,

Michael

Re: daily data to weekly

Vegar — Tue, 28 May 2019 15:11:21 GMT

The firts sorted value approach is easy to adjust.

=FirstSortedValue (pageCount, -reportDate)

Re: daily data to weekly

msmichael — Tue, 28 May 2019 16:17:06 GMT

Hi,

In my bar chart, I'm using below calculated dimension: (because I want to get weekly summary)

=WeekEnd(reportDate)

I want to embed the FirstSortedValue function into aggr, I'm using below expression:

=sum(aggr(FirstSortedValue(pageCount, -reportDate), reportId, WeekEnd(reportDate)))

However, I got "No data to display".. how to make it work?

my qvw attached..

thanks

Re: daily data to weekly

msmichael — Tue, 28 May 2019 16:21:23 GMT

Hi,

I figured, changed the expression a little bit, now it works.

=sum(aggr(FirstSortedValue(pageCount,-reportDate ), Week, reportId))

thanks for the help!

Michael

Re: daily data to weekly

Vegar — Tue, 28 May 2019 16:32:02 GMT

Great!
glad it worked out for you.