topic Help me with the Chart Expression. in QlikView

Help me with the Chart Expression.

Wed, 10 Aug 2011 06:22:09 GMT

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.

Help me with the Chart Expression.

IAMDV — Wed, 10 Aug 2011 08:53:46 GMT

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

Re: Help me with the Chart Expression.

Wed, 10 Aug 2011 09:23:17 GMT

Hi,

I have uploaded the sample file.

Thank you

Re: Help me with the Chart Expression.

Fri, 12 Aug 2011 05:25:39 GMT

Hi all,

Please post your ideas.

Thank you

Help me with the Chart Expression.

Fri, 12 Aug 2011 11:08:49 GMT

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.

Re: Help me with the Chart Expression.

Tue, 16 Aug 2011 13:52:13 GMT

Hi,

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

Thanks