Is it possible in pivot or straight table

[arulsettu]

hi,

i need to do a calculation in row wise

for example data is like this.     Account and Amount are fields available

ACCOUNT                                                  AMOUNT

Gross premiums                                            100

RI Share of premium-FAC & Prop. TTY            200

XOL Protection costs                                      300

Movement in unexpired premium                      250

what i need is

ACCOUNT                                                  AMOUNT

Gross premiums                                            100

RI Share of premium-FAC & Prop. TTY            200

XOL Protection costs                                      300

Net premium                                                sum of above 3 rows

Movement in unexpired premium                      250

Net earned premium                                    Net premium + Movement in unexpired premium

like this i have to do few more calculations within rows. is this possible to do? or any other alternate ways to do?

please suggest some ideas

Thanks

[danieloberbilli]

Would you like to have this in one table only? So these sub-totals must be exactly inbetween the list of accounts? 

[#3 · Accepted solution]

Hi you can do achieve your desired result in a table chart using interval match function. For details check out this blog post and technical brief by jmc‌

How IntervalMatch Solved My Profit and Loss Dilemma

How to Create a Profit and Loss Statement in QlikView‌

Hope this helps

Regards

Sana

[arulsettu]

if it is possible in between accounts field or any other alternate ways also fine

[danieloberbilli]

Thanks Sana for posting this - I wasn't aware of that blog post - looks great!

@Arul: that should cover everything you'll need as best-practice

[arulsettu]

but Net premium and Net earned premium  not available in account filed. can you tell me how to start?

thanks

[arulsettu]

the above example

Exec P&L Heading

Gross Sales

Sales Return

Off Invoice Discounts

Sales Promotions

NET SALES

Cost of Sales

Warehousing

Freight & Delivery

TOTAL COST OF SALES

GROSS MARGIN

Selling Expenses

General & Administrative

TOTAL OPERATING EXPENSES

OPERATING INCOME

Non-Operating Expenses

NET INCOME (LOSS)

NET SALES and TOTAL COST OF SALES and others are present in the field Exec P&L Heading i think

but i am having data like this

ACCOUNT                                                  AMOUNT

Gross premiums                                            100

RI Share of premium-FAC & Prop. TTY            200

XOL Protection costs                                      300

Movement in unexpired premium                      250

so how to do?

thanks

[arulsettu]

any ideas guys?

[#9]

I don't sure if that helpful for you.

But you can make a formula in each pivot - table, with the dimensions. On example:

I don't sure if that helpful for you.

But you can make a formula in each pivot - table, with the dimensions. On example:

=sum({$<pos_type={'Position_a'}, Year={$(=Only(Year))}, Quartal=,Month=>} Position_b) -

if (Only(Year) = $(vCurrentYear),

sum({$<pos_type={'Position_a'}, Year={$(=Only(Year)-1)}, Quartal=,Month=>} Position_b* YTDletztesYear),

sum({$<pos_type={'Position_a'}, Year={$(=Only(Year)-1)}, Quartal=,Month=>} Position_b))

The dimensions are: Position_a , Position_b, year, quartal, month. And the vCurrentYear is a fix variable(Set vCurrentYear = 2014;  // in the Script) And I used this formula have to make the sum of the privios year.

[#10]

And has you a formula, you could in the pivot table, you make this column drag diagonal or vertical wiht your mouse.