topic Re: Problem of calculation in QlikView

Problem of calculation

Fri, 11 Oct 2013 06:05:34 GMT

I have multiple tables in my database in qvd file my data corresponding to actual and budget is in the form of:

Table A Table B Table C

Col1 pk_Col2 pk_ Col2 Col4 pk_Col7 Col6 pk_Col7

Actual 1 1 500 A5 Total Assets A1

Budget 2 2 600 A6 Total Revenue A2

LEjan 3 3 700 A7 Current Assets A3

Lefeb 4 4 800 A8 Sales Direct A4

have to make a chart like:

Balancesheet

Actual Budget Prior Year Prior year%

total revenue calculation cal. cal. cal.

Current Liabilities cal. cal. cal. cal.

total Asset cal. cal. cal. cal.

where these calculation is based on the col4 data corresponding to the dimensions as in chart. And i made the neccessary relation between tables by making the field name common.

please help me in this task.

Re: Problem of calculation

Fri, 11 Oct 2013 06:30:00 GMT

Hi Poornima,

In a Pivot table,

you can add two dimensions in this order:

Col6,col1,

Then you can make col1 horizontal.

But, what is prior year and prior year %?

-Anju

Re: Problem of calculation

Fri, 11 Oct 2013 06:35:41 GMT

these all are calculation fields these data are not given in the col where actual and budget is given,

I have attached the database regarding these fields

Re: Problem of calculation

Fri, 11 Oct 2013 06:41:41 GMT

Ok.

How is prior year calculated?

Re: Problem of calculation

Fri, 11 Oct 2013 07:00:15 GMT

Hi,

Can you explain, How to calculate the Prior Year fields?

Re: Problem of calculation

saumyashah90 — Fri, 11 Oct 2013 07:04:22 GMT

Take Col1 and Col6 in dimension.

Put your Calculation in expression

When the chart appears,just click on the col1 header and drag it above the calculation part.
You will achieve your requirement

Re: Problem of calculation

Fri, 11 Oct 2013 07:18:50 GMT

My table B is as

My tableA where prior year and prior year % can be checked by quarterly, halfyearly and yearly

This is my table C which is forming an hierarchy for calculation:

Re: Problem of calculation

Fri, 11 Oct 2013 09:54:39 GMT

ok.. so we have table c for calculating prior year.

How do we calculate it?is it count of pk_dim_pov ..or something like that?

Re: Problem of calculation

Fri, 11 Oct 2013 12:04:31 GMT

Hii Anju,

The question i previously asked is solved for some extent. My new task is as follows,please help me:

I want to make a chart in which All the values come from table A stri_pov_code but not parent,actual and budget as dimensions and values of money_measure from table B for some measurement present in tableC str_coa_description.

How Can I make it?

Re: Problem of calculation

Mon, 14 Oct 2013 11:36:31 GMT

Hi,

In a pivot table,

you can add calculated dimension,

if(stri_pov_code<>'Parent',stri_pov_code) and tick 'Suppress when value is null' under Dimensions.

Can you please elaborate the other requirements.

-Anju