Forum Discussion
Helper IIQueries with overlapping date periods
Your data is too scarce to repro the full issue. for the "need to build a view showing metrics over a 45-day period, shifted weekly" you can probably create a calculated table first then add the measure:
date = var k = ADDCOLUMNS(FILTER(CALENDAR(DATE(2019,01,01),DATE(2020,01,08)),WEEKDAY([Date],2)=3),"date end",[Date]+44) return kWeeks back = CALCULATE(COUNTROWS('date'),FILTER('date',[Date]>=EARLIER([Date])))-1I can't figure out where the metrics info from, but you can manage the calendar table with your data to calculate them automatically.
Pbix attached.
Community Support
Your data is too scarce to repro the full issue. for the "need to build a view showing metrics over a 45-day period, shifted weekly" you can probably create a calculated table first then add the measure:
date = var k = ADDCOLUMNS(FILTER(CALENDAR(DATE(2019,01,01),DATE(2020,01,08)),WEEKDAY([Date],2)=3),"date end",[Date]+44)
return
kWeeks back = CALCULATE(COUNTROWS('date'),FILTER('date',[Date]>=EARLIER([Date])))-1
I can't figure out where the metrics info from, but you can manage the calendar table with your data to calculate them automatically.
Pbix attached.
As you know, Web Analytics solutions does not provide you data at the lowest granularity and therefore you can't sum non-summable metrics (like users) over periods. There isn't an easy solution to this. A few options are
- Use metrics that are summable (page views) instead of non-summable (users) for your analysis. PV *might* be a good proxy for your users but depends how much you can change your requirements
- Statistically calculate the overlapping on average and reduce it accordingly. So for if on a day you have 100 users but on a 45 days you have 4000 users (instead of 4500), download the data on a daily level, sum and reduce by 1-(4000/4500)=11.1%. It's an approximation of course.
- Use direct query connectors: there exists some connectors that allows to work on a direct query model for GA (and therefore they re-query every period for non-summable) but not sure for Adobe Analytics (try CDATA).
- And of course run every single query...probably it can be automated through Power Query, but I never tried. Still, however, this will be fairly slow as you will have to make 400 requests to AA.