Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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
Dear Sunny,
Expect you to suggest me an answer for the said issue as well.
Regards
Neville
Expect somebody to help on this!
Regards
Neville
Dear All!
Appreciate if I am forwarded with an answer!
Thanks in advance.
Neville
what is expected output?
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
Please reply on this!
Thanks in advance
Neville
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
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
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