Variance of Gross_Premium over Years

[nevilledhamsiri]

Hi,

Dear Friends,

Will you show me a set expression for the calculation of premium variance over the Years!. You may refer my P/Table along with script.

Directory;

LOAD BRANCH_CODE,

     CLASS_CODE,

     PRODUCT_CODE,

     POL_POLICY_NO,

     NAME,

     REF_NO,

     FROM_DATE,

     END_DATE,

     POL_SUM_INSURED,

     TR_DATE,

     YEAR(TR_DATE)AS TRN_YEAR,

     TR_TYPE,

     CP,

     RS,

     TC,

     (CP+RS+TC) AS GROSS_PREMIUM,

     TOTAL,

     MARKETING_PERSON,

     F17,

     F18,

     F19,

     F20,

     F21,

     F22,

     F23,

     F24,

     F25,

     F26,

     F27,

     F28,

     F29,

     F30

FROM

[..\policy_Registers for all Branches(2017).xlsx]

(ooxml, embedded labels, table is Sheet1);

Directory;

LOAD BRANCH_CODE,

     CLASS_CODE,

     PRODUCT_CODE,

     POL_POLICY_NO,

     NAME,

     REF_NO,

     FROM_DATE,

     END_DATE,

     POL_SUM_INSURED,

     TR_DATE,

     YEAR(TR_DATE)AS TRN_YEAR,

     TR_TYPE,

     CP,

     RS,

     TC,

     (CP+RS+TC) AS GROSS_PREMIUM,

     TOTAL,

     MARKETING_PERSON,

     F17,

     F18,

     F19,

     F20,

     F21,

     F22,

     F23,

     F24,

     F25,

     F26,

     F27,

     F28,

     F29,

     F30

FROM

[..\policy_Registers for all Branches(2016).xlsx]

(ooxml, embedded labels, table is Sheet1);

Directory;

LOAD BRANCH_CODE,

     CLASS_CODE,

     PRODUCT_CODE,

     POL_POLICY_NO,

     NAME,

     REF_NO,

     FROM_DATE,

     END_DATE,

     POL_SUM_INSURED,

     TR_DATE,

     YEAR(TR_DATE)AS TRN_YEAR,

     TR_TYPE,

     CP,

     RS,

     TC,

     (CP+RS+TC) AS GROSS_PREMIUM,

     TOTAL,

     MARKETING_PERSON,

     F17,

     F18,

     F19,

     F20,

     F21,

     F22,

     F23,

     F24,

     F25,

     F26,

     F27,

     F28,

     F29,

     F30

FROM

[..\policy_Registers for all Branches(2015).xlsx]

(ooxml, embedded labels, table is Sheet1);

[nevilledhamsiri]

Dear Sunny,

Expect you to suggest me an answer for the said issue as well.

Regards

Neville

[nevilledhamsiri]

Expect somebody to help on this!

Regards

Neville

[nevilledhamsiri]

Dear All!

Appreciate if I am forwarded with an answer!

Thanks in advance.

Neville

[Kushal_Chawda]

what is expected output?

[nevilledhamsiri]

Dear Kushal,

What I need is to highlight the variance of Gross premium from 2015 to 2016 & 2016 to 2017 etc. When more Years are involved, is there a way of finding this out by a single expression? If so what it is?

Thanks

Neville

[nevilledhamsiri]

Please reply on this!

Thanks in advance

Neville

[oscar_ortiz]

Neville,

Really don't understand what you are trying to accomplish, so I'll offer a guess.

Are you simply trying to determine the difference of your Gross value year to year?  If so you may want to try an expression using the Above() function.

In this example I use the above function to find the "Prior" value then simply subtract the two to find the difference.  But of course you can expand on that to create the expression that works for you.

Good luck

Oscar

[nevilledhamsiri]

Dear Oscar,

The figures you bring in to variance column is what I need. Please write me the expression for that & wish to find out the expression you wrote to get the prior figures as well.

Regds

Neville

[oscar_ortiz · Accepted solution]

Neville,

All I've done at this point is used the Above() function with the same expression that I used for my Gross expression:

Above( Sum(Gross) )

You'll want to use whatever expression work for you:

Above( YourExpressionGoesHere )

I labeled that expression Prior then created a new expression that will find the difference:

[Gross] - [Prior]

I used the column labels in my third expression and labeled it Variance.

Good Luck

Oscar