Adding data of prev. years or aggregating prev. years data into current year

[#1]

Hi,

     Not sure the subject is relevant. I want to achieve following results:

     I have data sampled  from past year till this year. But when displaying the data either in a graph or a pivot ,I am displaying only current year and months. For which the appropriate data is getting displayed. What I want is the prev. years data should be aggregated i.e added up into this data and displayed.

In the attached ex. there is a policy count of 117 till dec. 2011. And for month of Jan 2012 the count is 22, for feb 2012 it is 58 and so on...

When the graph is displayed for Jan 2012,Feb 2012 on x axis and policy count on y axis. I could see 22 and 58, there is a 219 policy count shown under no date i.e blank. When I suppress the blanks this count ( 219) disappears. This count of 219 should be added up in the count of jan 2012. as illustrated below.

In the graph I should see for Jan 2012 the count of (219+22=241) for feb 2012 ( 241+58=299) etc.

In the pivot I should see for Jan 2012 and for the dimension (keyagebucket) ( 13+5 = 18) for feb 2012 ( 18+4=22) etc

Please help how can this be achieved.

Thanks

Lax

[CELAMBARASAN]

Hi,

     For past 4 quarters

     Use the below expression

     =Sum({<EXTRACTIONDATE={">=$(=TimeStamp(QuarterStart(Max(EXTRACTIONDATE),-4)))"}>}MTD_COUNT_SURRENDERABLE)+Alt(Above(TOTAL [Surr1 policy count]),$(=Sum({1<EXTRACTIONDATE={"<$(=TimeStamp(QuarterStart(Max(EXTRACTIONDATE),-4)))"}>} MTD_COUNT_SURRENDERABLE)))

Celambarasan

[#3]

Hi,

     I am getting incorrect result with the set analysis. Could you let me know what could be the issue.

=

Sum({<EXTRACTIONDATE={">=$(=TimeStamp(YearStart(Max(EXTRACTIONDATE))))"}>}MTD_COUNT_SURRENDERABLE)+Alt(Above(TOTAL [Surr1 policy count]),$(=Sum({1<EXTRACTIONDATE={"<$(=TimeStamp(YearStart(Max(EXTRACTIONDATE))))"}>} MTD_COUNT_SURRENDERABLE))

Thanks

Lax

[CELAMBARASAN]

Hi,

     You used your expression name as [Surr1 Policy count] but inside the expression you used as [Surr1 policy count] it case sensitive check the case of the P in the expression label.

Celambarasan

[#5]

Thanks a lot, Just wanted to know if there can be addition of months, or years in this set analysis. for ex. if I want to start it from Oct 2011 or Nov. 2011. is it possible ?

Regards

Lax

[CELAMBARASAN · Accepted solution]

Hi,

Ya you can do that.

Use QuaterStart for Deciding how many quaters you want.

     Example:For last 4 quarters

=Sum({<EXTRACTIONDATE={">=$(=TimeStamp(QuarterStart(Max(EXTRACTIONDATE),-4)))"}>}MTD_COUNT_SURRENDERABLE)+Alt(Above(TOTAL [Surr1 policy count]),$(=Sum({1<EXTRACTIONDATE={"<$(=TimeStamp(QuarterStart(Max(EXTRACTIONDATE),-4)))"}>} MTD_COUNT_SURRENDERABLE)))

Use MonthStart for deciding how many months you want.

Example:for last 6 months

=Sum({<EXTRACTIONDATE={">=$(=TimeStamp(MonthStart(Max(EXTRACTIONDATE),-6)))"}>}MTD_COUNT_SURRENDERABLE)+Alt(Above(TOTAL [Surr1 policy count]),$(=Sum({1<EXTRACTIONDATE={"<$(=TimeStamp(

MonthStart

(Max(EXTRACTIONDATE),-6)))"}>} MTD_COUNT_SURRENDERABLE)))

Hope it helps

Celambarasan

[#7]

Hi,

To this chart I added another exp. and my chart went haywire, as shown below

I have modified the above query and using the below one. I am getting correct display and data.

1. Sum({<EXTRACTIONDATE={">=$(=MonthStart(Min(EXTRACTIONDATE),24))"}>}MTD_SURRABLE)

+Alt(Above([surrenderable policies]),$(=Sum({1<EXTRACTIONDATE={"<$(=MonthStart

(Min(EXTRACTIONDATE),24))"}>} MTD_SURRABLE)))

Now I have added new exp. as follows

2.if(aggr(sum({<DATE_FYYYY= {$(CFYear)}>}MTD_SURR),DATE_FYYYYMM)>0,

Rangesum(above(Sum ({<DATE_FYYYY= {$(CFYear)}>}MTD_SURR),0,rowno())))

The

n  I get the graph 1

If I put the new exp. with full accumulation as

3. sum({<DATE_FYYYY= {$(CFYear)}>}MTD_SURR)

Then I get graph 2. which is correct but it displays the record (in blue). Here The data should not be displayed for

for Year and month 2012-03 onwards. only the data of exp. 1 should be displayed.

Graph 1

Graph 2

Thanks

Lax

[CELAMBARASAN]

Hi,

     This is the full accumulation functionality.If you want to restrict it you have to go by expression only.like

     if(DimensionField<Date,alt(above([Expression Name]),0)+sum({<DATE_FYYYY= {$(CFYear)}>}MTD_SURR),0) without full accumulation.

Celambarasan

[#9]

Hi,

          If I write following set analysis as suggested

if(DATE(EXTRACTIONDATE,'DD-MM-YYYY')<'31-03-2012',alt(above([surrendered policies]),0)+sum({<DATE_FYYYY= {$(CFYear)}>}MTD_SURR),0)

With this no data is displayed for the expression and the other data get skewed like shown in graph 1.

The reason for this is that the data is available from 2009-04 to 2014-02. But for exp. 1 we are displaying the data from 2011-04 till 2013-03 as shown in graph 2. and for exp 2. the data is to be displayed from 2011-04 till 2012-02.

More over the exp. 2 shows accumulation of only last 2 months , i.e for 2012-02 it shows sum of 2012-02 and 2012-01. It does not show accumulation of month on month.

Thanks,

Lax

[CELAMBARASAN]

Hi,

Try with this

     if(DATE(EXTRACTIONDATE,'DD-MM-YYYY')<=Date#('31-03-2012','DD-MM-YYYY'),alt(above([surrendered policies]),0)+sum({<DATE_FYYYY= {$(CFYear)}>}MTD_SURR),0)

Celambarasan

[#11]

Hi,

      Thats great !. This solves the problem of my 2nd exp. but my 1st exp is now skewed.

It shows data from 2010-04 ( instead of showing from 2011-04) which is flat till 2011-03 and then shows as per required, just as showed in graph 1.

set analysis for exp 1. is

Sum({<EXTRACTIONDATE={">=$(=MonthStart(Min(EXTRACTIONDATE),24))"}>}MTD_SURRABLE)

+Alt(Above([surrenderable policies]),$(=Sum({1<EXTRACTIONDATE={"<$(=MonthStart

(Min(EXTRACTIONDATE),24))"}>} MTD_SURRABLE)))

Does it requires some change ?

Thanks,

Lax