topic Re: Dimension in QlikView

Dimension

upaliwije — Sat, 12 Jul 2014 12:05:16 GMT

I have a pivot table as given below where the dimension field is Description

Description Actual Budget

Sales 10000 15000

less Purchase 6000 10000

Gross Profit 4000 5000

Staff Expense 2000 3000

Other Expense 1000 1000

Total Expenses 3000 4000

Profit 1000 1000

Expression for Actual is sun(actual) Budget is sum(Budget)

Now I want to calculate the rows highlighted in Red

Gross Profit= Sales-Purchase

Total Expense = Staff Expense+Other Expense)

Profit= Gross profit-Total Expense

Pls help me to calculate above rows in my pivot table

Re: Dimension

PrashantSangle — Sat, 12 Jul 2014 12:10:21 GMT

Hi,

is this same thread

p &L

As per my knowledge this is not possible.

You can not use Expression in between Dimension Values .

So try with some new logic to do the required output

Regards.

Re: Dimension

MK_QSL — Sat, 12 Jul 2014 12:15:26 GMT

How is your datamodel?

Re: Dimension

upaliwije — Sat, 12 Jul 2014 12:16:29 GMT

Thanks max

What logic would you suggest?

Re: Dimension

swuehl — Sat, 12 Jul 2014 12:19:00 GMT

Have a look at

http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/10/how-intervalmatch-solved-my-profit-and-loss-dilemma

How to Create a Profit and Loss Statement in QlikView

Re: Dimension

upaliwije — Sat, 12 Jul 2014 12:20:20 GMT

My data model is based from an excel sheet as follows

Description Actual Budget

Sales

Purchases

Sales

Staff Expenses

Other Expense

Puchase

So load the excel sheet and create the pivot table

Re: Dimension

MK_QSL — Sat, 12 Jul 2014 12:22:23 GMT

Can you provide sample data file in excel... I would love to work on this...

Re: Dimension

upaliwije — Sat, 12 Jul 2014 12:23:22 GMT

Hi Swvehl

I have read it. Is there any simple way to do it ?

Re: Re: Dimension

upaliwije — Sat, 12 Jul 2014 12:27:11 GMT

Hi Manish

I attaching it

Re: Re: Re: Dimension

MK_QSL — Sat, 12 Jul 2014 13:01:13 GMT

Check enclosed file....

Re: Re: Dimension

upaliwije — Sat, 12 Jul 2014 17:33:33 GMT

Thanks a lot Manish

One more thing If I want to add following columns to Pivot Table How can I modify the expression

KP1 Actual Budget Variance Ach %

Sales 1000 2000 1000 50%

Variance=Budget-Actual

Ach % = Actaul/Budget *100