Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Help me with the Chart Expression.

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.

5 Replies
Luminary
Luminary

Help me with the Chart Expression.

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

Not applicable

Re: Help me with the Chart Expression.

Hi,

I have uploaded the sample file.

Thank you

Not applicable

Re: Help me with the Chart Expression.

Hi all,

Please post your ideas.

Thank you

Not applicable

Help me with the Chart Expression.

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.

Not applicable

Re: Help me with the Chart Expression.

Hi,

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

Thanks

Community Browser