Set analysis of Current 3 months from the given Date

[#1]

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

[JonnyPoole · Accepted solution]

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)

[#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.

[JonnyPoole]

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

[#5]

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

[JonnyPoole]

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)  

[#7]

Thanks a lot for your help Jonthan.