daily data to weekly

[msmichael]

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

[msmichael]

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

[Vegar · Accepted solution]

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

[msmichael]

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

[Vegar]

The firts sorted value approach is easy to adjust. 

=FirstSortedValue (pageCount, -reportDate)

[msmichael]

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

[msmichael]

Hi,

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

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

thanks for the help!

Michael 

[Vegar]

Great!
glad it worked out for you.