Growth over two Years

[nevilledhamsiri]

Hi

Appreciate if the reference is made to propose how best the growth could be computed based on the expression applied for the calculation of MO, NM & Total sales. Expressions applied as follows!

SUM({<MO_NM_SALES={'MOTOR'},ME_CODE>}TOTAL)

SUM({<MO_NM_SALES={'NON_MOTOR'},ME_CODE>}TOTAL)

SUM({<MO_NM_SALES={'MOTOR'},ME_CODE>}TOTAL)+SUM({<MO_NM_SALES={'NON_MOTOR'},ME_CODE>}TOTAL)

Thanks

Neville

[nevilledhamsiri]

Dear All!

Please see to this.

I urgently requires this calculation to be done!

Best Regards

Neville

[Digvijay_Singh]

Do you have master calendar with  date related fields associated to your fact table date field? You need to add date Year filter in all your expressions, may be like this to calculate the growth % from last year to this year-

(SUM({<MO_NM_SALES={'MOTOR'},ME_CODE,Year={"=$(=Year(DateField))"}>}TOTAL)

-

SUM({<MO_NM_SALES={'MOTOR'},ME_CODE,Year={"=$(=Year(DateField)-1)"}>}TOTAL))

/

SUM({<MO_NM_SALES={'MOTOR'},ME_CODE,Year={"=$(=Year(DateField)-1)"}>}TOTAL)

Do you want Yeartodate growth? For that you would need different set expression, something like Date >=Yearstart and <today() or use of yeartodate function.

[nevilledhamsiri]

Hi Digvijay

When this expression given, an error appear.Can you please help me to correct this. Following calender  I have loaded.

Cant we use sum( above) function here?

Let vStartDate = Floor(MakeDate(2017,1,1));

Let vEndDate = Floor(Today());

Let vDiff = vEndDate-vStartDate +1;

CALENDAR:

LOAD

*,

Year & '' & Month  As %YearMonth,

Year & '-' & Quarter As YearQuarter,

WeekYear & '-' & Num(Week, '00') As YearWeek;

LOAD

DateID,

Date(DateID) As DATE,

Year(DateID) As Year,

Num(Month(DateID), '00') As Month,

Month(DateID) As Monthh,

Day(DateID) As Day,

Week(DateID) As Week,

'Q' &Ceil(Month(DateID)/3) As Quarter,

WeekYear(DateID) As WeekYear,

-Year2Date(DateID) As YTD_Flag,

-Year2Date(DateID, -1) As LYTD_Flag,

weekday(DateID) AS Weekday,

autonumber(weekyear(DateID) &'|' & week(DateID), '_WeekSerial') AS _WeekSerial;

LOAD

RecNo()-1+$(vStartDate) As DateID

AutoGenerate($(vDiff));

Thanks

Neville

[Digvijay_Singh]

Can you share expected output and sample data to see on which chart you are trying this.

[nevilledhamsiri]

I have attached the excel & P/T done. Script too given

Thanks

Neville

Here it is a same file with data for two Years!

POLICY_REGISTER_2018:

LOAD BRANCH_CODE,

     CLASS_CODE,

     PICK(MATCH(CLASS_CODE,'MC','M4','M3')+1,'NON_MOTOR','MOTOR','MOTOR','MOTOR')AS MO_NM_SALES, 

     PRODUCT_CODE,

     POL_POLICY_NO,

     NAME,

     REF_NO,

     FROM_DATE,

     END_DATE,

     POL_SUM_INSURED,

     TR_DATE,

     MONTH(TR_DATE) AS TR_MONTH,

     YEAR(TR_DATE)AS TR_YEAR,

     TR_TYPE,

     CP,

     RS,

     TC,

     TOTAL,

     MARKETING_PERSON,LEFT(MARKETING_PERSON,5) AS  ME_CODE

FROM  (ooxml, embedded labels, table is Sheet1);

[nevilledhamsiri]

Shorten Excel attached for your ref

Please suggest the growth Jan_2018 vs Jan _2017 & same for Feb as well based on the following expressions

SUM({<MO_NM_SALES={'MOTOR'},ME_CODE>}TOTAL)

SUM({<MO_NM_SALES={'NON_MOTOR'},ME_CODE>}TOTAL)

SUM({<MO_NM_SALES={'MOTOR'},ME_CODE>}TOTAL)+SUM({<MO_NM_SALES={'NON_MOTOR'},ME_CODE>}TOTAL)

Thanks

Neville

[Digvijay_Singh]

May be like this -

[nevilledhamsiri]

Please send me the expression in a notepad for growth calculation. I am using a personnel edition.

Thanks

Neville

[nevilledhamsiri]

If Year/ Month dimensions are  arranged  vertically, results re not produced on the given growth expression. How this could be corrected?

SUM({<MO_NM_SALES={'MOTOR'},BRANCH_CODE>}TOTAL)/ABOVE(SUM({<MO_NM_SALES={'MOTOR'},BRANCH_CODE>}TOTAL))-1

Regds

Neville