Help me with the Chart Expression.

[#1]

Hi all,

I am trying to plot a metrics called joining rate. The calculation of joining rate is as follows.

For a particular MONTH:

If you want to calculate the joining rate for a particular month, you have to aggregate the ‘number of new joinees’ by taking the eighth month prior to the selected month as lower limit and third month prior to the selected month as upper limit.

For example, suppose I want to calculate the joining rate for the month of December. Then I have to add all the ‘number of new joinees’ values from April to September. Similarly, if I have to find out the value for January, I have to add all the ‘number of new joinees’ values from May to October the previous year.

For a particular QUARTER:

My dashboard has the data for only 3 years 2009,2010 and 2011. If you want to calculate the joining rate for a particular Quarter, you have to take the eighth month prior to the first month of Quarter as lower limit and the third month prior to the last month of Quarter as upper limit.

For example, if I want to calculate the joining rate for 2010 Q3, I have to aggregate the respective ‘number of new joinees’ values from November 2009 to April 2010.

For a particular YEAR:

Similarly for calculating the joining rate of a particular year, you have to take the eighth month prior to the first month of the year as lower limit and the third month prior to the last month of the year as upper limit.

For example, if I want to calculate the joining rate for 2010, I have to aggregate the respective ‘number of new joinees’ values from May 2009 to September 2010.

i have the data for month, year, quarter and number of new joinees.

I want all the 3 views in the same chart or as separate charts for Month, Quarter and Year. Please help me with the same.

Thank you.

[IAMDV]

Hi,

Thanks for the detailed explanation. Please can you alsoupload sample QV document with scrambled data? It will be easier to work withyour example...

Cheers - DV

[#3]

Hi,

I have uploaded the sample file.

Thank you

[#4]

Hi all,

Please post your ideas.

Thank you

[#5]

Hi,

i think the following should do the job for "For a particular MONTH":

1. Create 2 variables

     vStartMonth =Date(Addmonths(MakeDate(year,num(month(Date#(month_sid,'YYYYMM')))),-9),'YYYYMM')

     vEndMonth =Date(Addmonths(MakeDate(year,num(month(Date#(month_sid,'YYYYMM')))),-2),'YYYYMM')

2. Now you can use the follwoing statements to get the result:

     =Sum({$<month_sid = {">$(startMonthMonth)<$(endMonthMonth)"}>} number_of_new_joinees)

Should be simular for Month and Year.

Best Regards.

[#6]

Hi,

Its not working for me. can u send the sample file?

Thanks