Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis of Current 3 months from the given Date

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

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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)

View solution in original post

6 Replies
JonnyPoole
Employee
Employee

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)

Not applicable
Author

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

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

Not applicable
Author

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

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)  

Not applicable
Author

Thanks a lot for your help Jonthan.