topic Re: Is it possible in pivot or straight table in QlikView

Is it possible in pivot or straight table

arulsettu — Thu, 30 Apr 2015 04:21:17 GMT

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

Re: Is it possible in pivot or straight table

danieloberbilli — Thu, 30 Apr 2015 04:25:04 GMT

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

Re: Is it possible in pivot or straight table

Thu, 30 Apr 2015 04:25:53 GMT

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

Re: Is it possible in pivot or straight table

arulsettu — Thu, 30 Apr 2015 04:30:17 GMT

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

Re: Is it possible in pivot or straight table

danieloberbilli — Thu, 30 Apr 2015 04:36:00 GMT

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

Re: Is it possible in pivot or straight table

arulsettu — Thu, 30 Apr 2015 04:41:15 GMT

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

thanks

Re: Is it possible in pivot or straight table

arulsettu — Thu, 30 Apr 2015 04:57:18 GMT

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

Re: Is it possible in pivot or straight table

arulsettu — Thu, 30 Apr 2015 05:24:14 GMT

any ideas guys?

Re: Is it possible in pivot or straight table

Thu, 30 Apr 2015 05:47:32 GMT

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.

Re: Is it possible in pivot or straight table

Thu, 30 Apr 2015 05:58:35 GMT

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

Re: Is it possible in pivot or straight table

arulsettu — Thu, 30 Apr 2015 07:05:33 GMT

anyone did this before?

Re: Is it possible in pivot or straight table

ankitaag — Thu, 30 Apr 2015 07:47:40 GMT

Hi,

For this kind of P&L you need to make some big calculations .

Please refer the attached application.

Re: Is it possible in pivot or straight table

arulsettu — Thu, 30 Apr 2015 07:55:24 GMT

Thanks Ankita,

i have many calculations like this i want to know how they calculating with P&L

ex: a+b-c

is this working in intervalmatch?

Re: Is it possible in pivot or straight table

ankitaag — Thu, 30 Apr 2015 08:05:37 GMT

I haven't use intervalmatch. Can you add some data in the inline in the application whivh I have attached. and let me know the calculations you want to do

Re: Is it possible in pivot or straight table

Thu, 30 Apr 2015 08:24:52 GMT

Hi arul,

We have implemented several large application containing complex p&l, balance sheet and cash-flow statements and al of them we have done using the interval match process mentioned in the technical brief. If you follow the directions in the document you should be able to get your report done

Re: Is it possible in pivot or straight table

arulsettu — Thu, 30 Apr 2015 08:32:15 GMT

Hi Sana,

i dont have fields like Exec P&L Level and Exec P&L Calculation and ExecPLStart and ExecPLEnd

and Reporting Code also. shall ask DB admin to insert these fields. and i studied that document. but i cant able to understand how its calculating NET SALES. is it based on ExecPLStart and ExecPLEnd. can you tell me how its calculating?

Thanks

Re: Is it possible in pivot or straight table

Thu, 30 Apr 2015 08:48:05 GMT

Hi,

You do not need to necessarily have such fields. I'm guessing your account names already exist in the database? create a mapping table which will contain unique ids for each account.

ACCOUNT A/C ID

Gross premiums 1

RI Share of premium-FAC & Prop. TTY 2

XOL Protection costs 3

no create another table which will contain your your report format

report header report_start report_end

Gross premiums 1 1

RI Share of premium-FAC & Prop. TTY 2 2

XOL Protection costs 3 3

Net Premium 1 3

Once you do this and do the interval match you can use report header in your straight / pivot table as dimension and sum(Amount) or other similar expression.

Re: Is it possible in pivot or straight table

arulsettu — Thu, 30 Apr 2015 08:54:28 GMT

you mean creating inline table? and i need Net Premium value by calculating

Gross premiums+RI Share of premium-FAC & Prop. TTY-XOL Protection costs

how interval match will do this?

Re: Is it possible in pivot or straight table

Thu, 30 Apr 2015 09:18:48 GMT

Is the amount that needs to be subtracted already negative ? otherwise you can multiply by -1. For example when we did the p&l we created flags in the script which later helped us identify which filed values to addd and which ones to subtract

Re: Is it possible in pivot or straight table

Thu, 30 Apr 2015 09:22:27 GMT

doesn't necessarily have to be inline..you can load from excel. For the report ids we created mapping tables inline then mapped it to the data we loaded from our database