I am attaching one application in which i want the summation of values based on summation of the premium but as per the selection of the month.
Conditions are...
(1) in YTD i will consider the policy and Sum Assured only once depending upon the summation of the premium. Means if summation of premium of policy is Positive then i will count that policy as 1 and sum assured also only 1 though policy may repeat any number of time.
E.g. Policy 12344444 is repeated 4 times and summation of the overall premium is zero. So in this scenario i will neither consider that policy not Sum Assured.
Where as policy number 15555523 is also repeated 4 times but here the summation of that policy overall premium is in - 40000 so in this scenario i will count that policy only once like -1 and sum assured as -15000.
For policy 12345678 summation of the overall premium is Positive so i will count that policy 1 and sum assured as 74224.
(2) In MTD i will consider the the premium of one policy for that month only and depending upon the summation of the premium i will count NOP and Sum assured.
E.g. for policy number 12344444 in the month 1 because the summation of the premium is positive i will count the policy as 1 and sum assured as 742224. But in the 2 month because summation of the premium is in negative i will count same policy as -1 and sum assured as -74224.
Policy number 15555523 is repeated 3 times in 1 month and the summation of the premium is -20000 so i will count that policy as -1 and sum assured as -15000...
Can you please help to resolve the issue. Expected out-put is also shared in a excel.