Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
I want to write a set analysis for the below scenario.
Measure:Metric_Data, Metric={'Unique visitors'},
I want to find out the average of Metric_Data (measure) of the given metric for the current 3 months from the given date subtract avg of those three month from the previous year.
(Ex: (Avg (Jan 15: Mar 15) -Avg (Jan 14: Mar 14) / Avg (Jan 14: Mar 14).
Result: avg(3000,3300,3000)-avg(1000,1000,1000)/avg(1000,1000,1000) Metric={'Unique visitors'}
Please,find the attached data file
With the following variables:
vCurrentDate: max( {1} METRIC_DATE)
vPYCurrentDate: AddYears(max( {1} METRIC_DATE),-1)
Then use this expression:
(
(sum( {<METRIC={'Unique Visitors'},METRIC_DATE= {">=$(=AddMonths($(vCurrentDate),-2))<=$(=$(vCurrentDate))"} >} METRICS_DATA) / 3)
-
(sum( {<METRIC={'Unique Visitors'},METRIC_DATE= {">=$(=AddMonths($(vPYCurrentDate),-2))<=$(=$(vPYCurrentDate))"} >} METRICS_DATA) / 3)
)
/
(sum( {<METRIC={'Unique Visitors'},METRIC_DATE= {">=$(=AddMonths($(vPYCurrentDate),-2))<=$(=$(vPYCurrentDate))"} >} METRICS_DATA) / 3)
With the following variables:
vCurrentDate: max( {1} METRIC_DATE)
vPYCurrentDate: AddYears(max( {1} METRIC_DATE),-1)
Then use this expression:
(
(sum( {<METRIC={'Unique Visitors'},METRIC_DATE= {">=$(=AddMonths($(vCurrentDate),-2))<=$(=$(vCurrentDate))"} >} METRICS_DATA) / 3)
-
(sum( {<METRIC={'Unique Visitors'},METRIC_DATE= {">=$(=AddMonths($(vPYCurrentDate),-2))<=$(=$(vPYCurrentDate))"} >} METRICS_DATA) / 3)
)
/
(sum( {<METRIC={'Unique Visitors'},METRIC_DATE= {">=$(=AddMonths($(vPYCurrentDate),-2))<=$(=$(vPYCurrentDate))"} >} METRICS_DATA) / 3)
Hi Jonathan,
Thanks for your quick reply.
I have a Date in the format 03/01/2015 and if i am writing Max(03/01/2015) it is giving 3/1/2015.In the list box it is Date is 03/01/2015.It's not matching the value in the list box.Hence it is not pulling the value.
Exp: =addmonths(max(COMMUNICATIONS_DIGITAL_METRICS_METRIC_DATE),-2).
Please,suggest me in this case.
you can try using the date() function to match the data type
change:
addmonths(max(COMMUNICATIONS_DIGITAL_METRICS_METRIC_DATE),-2)
to
date(addmonths(max(COMMUNICATIONS_DIGITAL_METRICS_METRIC_DATE),-2),'DD/MM/YYYY')
if you have a sample that shows dd/mm/yyyy then please share as well. Earlier sample data had d/mm/yyyy
Hi Jonthan,
Below is the expression for avg of current 3 months data.
=avg( {<COMMUNICATIONS_DIGITAL_METRICS_METRIC = {'Unique Visitors'},
COMMUNICATIONS_DIGITAL_METRICS_METRIC_DATE = {">=$(=Date(addmonths(max(COMMUNICATIONS_DIGITAL_METRICS_METRIC_DATE),-2),'MM/DD/YYYY'))"}>} COMMUNICATIONS_DIGITAL_METRICS_DATA) it gives 3100
How can I do it for Previous year for the same months?? (sample data I have provided to you).
Can you send me the expression for that??
How about this ?
=avg( {<COMMUNICATIONS_DIGITAL_METRICS_METRIC = {'Unique Visitors'},
COMMUNICATIONS_DIGITAL_METRICS_METRIC_DATE = {">=$(=Date(addmonths(max(COMMUNICATIONS_DIGITAL_METRICS_METRIC_DATE),-14),'MM/DD/YYYY'))<=$(=Date(addmonths(max(COMMUNICATIONS_DIGITAL_METRICS_METRIC_DATE),-12),'MM/DD/YYYY')) "}>} COMMUNICATIONS_DIGITAL_METRICS_DATA)
Thanks a lot for your help Jonthan.