subtraction and division in pivot table

[arulsettu]

Hi,

     I am trying to do profit and loss statment. here in pivot doing row wise calculation using flags. my issue is when i try to subtract two rows i am getting same value for every rows. and for division also. when select particular lob it showing correct vale.

please check the sample app and suggest some idea. i am really stuck here

[arulsettu]

here is the excel file

[senpradip007]

You can get help from How to Create a Profit and Loss Statement in QlikView thread.

[arulsettu]

ya i followed but doing some mistake i dont know what it is

[arulsettu]

any one have any idea how to sutract based one flag?

[arulsettu]

any one check the excel. i want like this gross premium/net premium in net retention ratio

plz some one suggest any idea its urgent

Thanks

[arulsettu]

i have tried this

if([Clockwise Calculation]='nr',(sum({<TD_DRCR_FLAG={'D'},[Cloclwise Heading]={'Net premium'}>}TD_AMT_LC_1)-sum({<TD_DRCR_FLAG={'C'},[Cloclwise Heading]={'Net premium'}>}TD_AMT_LC_1))/

(sum({<TD_DRCR_FLAG={'D'},[Cloclwise Heading]={'Gross premiums'}>}TD_AMT_LC_1)-sum({<TD_DRCR_FLAG={'C'},[Cloclwise Heading]={'Gross premiums'}>}TD_AMT_LC_1))

)

but no result. any one suggest some idea plz..

Thanks

[arulsettu]

any help guys? its really urgent....

[sunny_talwar]

That is what you are expression to do:

if([Clockwise Calculation]='c',sum({<TD_DRCR_FLAG={'D'}>}TD_AMT_LC_1)-sum({<TD_DRCR_FLAG={'C'}>}TD_AMT_LC_1),

if([Clockwise Calculation]='d',sum({<TD_DRCR_FLAG={'D'},TBAL_CAL_YEAR={$(=max(TBAL_CAL_YEAR)-1)}>}TD_AMT_LC_1)-sum({<TD_DRCR_FLAG={'C'},TBAL_CAL_YEAR={$(=max(TBAL_CAL_YEAR)-1)}>}TD_AMT_LC_1),

if([Clockwise Calculation]='ui',sum({<TD_DRCR_FLAG={'D'}>}TD_AMT_LC_1)-sum({<TD_DRCR_FLAG={'C'}>}TD_AMT_LC_1),

if([Clockwise Calculation]='id',$(underwriting_income)-$(=underwriting_income_pre_year),

if([Clockwise Calculation]='nu',($(underwriting_income)-$(underwriting_income_pre_year))/(sum({<TD_DRCR_FLAG={'D'},TBAL_CAL_YEAR={$(=max(TBAL_CAL_YEAR)-1)}>}TD_AMT_LC_1)-sum({<TD_DRCR_FLAG={'C'},TBAL_CAL_YEAR={$(=max(TBAL_CAL_YEAR)-1)}>}TD_AMT_LC_1))*100,

if([Clockwise Calculation]='nr',($(net_premium)/$(gross_premium)*100),

if([Clockwise Calculation]='glr',$(incrued_claims)/$(gross_premium)*100

)))))))

Looking just at the bold part, you are telling your expression that whenever you see Clockwise Calculation = c, do sum({<TD_DRCR_FLAG={'D'}>}TD_AMT_LC_1)-sum({<TD_DRCR_FLAG={'C'}>}TD_AMT_LC_1). This is probably giving you the result based on what you have given it.

Now the question is what is the expected output??? Without knowing the expected output, it will be hard to help.

[arulsettu]

Hi Sun india,

                  sum({<TD_DRCR_FLAG={'D'}>}TD_AMT_LC_1)-sum({<TD_DRCR_FLAG={'C'}>}TD_AMT_LC_1)

above is getting value from debit-credit.

d-means debit

c-credict

its coming from same field  TD_AMT_LC_1

see the above pic or the app Net retention ratio i need to calculate

for that net premium/gross premium is the formula (its like calculating profit and loss in row wise)

can you please tell me how to do it?

plz check above excel also

Thanks