Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
samuelandlopes
Contributor

personalised dimension

Hello all,

i need help with a pivot table:

I need to separate in dimension:

SALES: DIMENSION;

COST: DIMENSION;

GROSS PROFIT: SALES + COST

RUNNING EXPENSES: DIMENSION

EBIT: GROSS PROFIT + RUNNING EXPENSES

HEADCOUNT: DIMENSION

How could I do this in a pivot table?

excel:

qlik:

tks

Tags (1)
9 Replies
Partner
Partner

Re: personalised dimension

You can use valuelist for cases where custom dimensions are required. See below

ValueList() – For those tricky situations

But in your case why do you need custom dimensions? You can use a standard pivot table

you can put all 6 as expressions. your site (the 1,2s) as the dimension

e.g. like below

Capture.PNG

and drag the expressions into rows and dimension as a column to create below

Capture.PNG

But if i missed something. value list would be the way to go

samuelandlopes
Contributor

Re: personalised dimension

tks for info Dilip.

I was thinking about that but the problem is that if I add a sub group dimension, it's not possible to pivot.

do you have a solution for this?

this is ok:

i need to pivot: collapse sub_group_eng

Partner
Partner

Re: personalised dimension

Not sure exaclty what you want to achieve.If you share a sample excel table or something i can help further

but you can pivot. see below a few things

Capture.PNG

Capture.PNG

Capture.PNG

samuelandlopes
Contributor

Re: personalised dimension

it works if i put in horizontal the sub group:

but I need in vertical with + sign to expande and collapse.

Partner
Partner

Re: personalised dimension

Ah ok. then you might need the value list option which i shared before.

basically you create custom dimensions on the fly and within a single expression for each 'dimension'  like Sales you give the formula for each one.

let me know if you have troubles

chrismarlow
Valued Contributor

Re: personalised dimension

Hi Samuel,

Below holds if your facts are additive, so Gross Profit =Sales minus Cost of Sales per my accountancy text book, but what you have is fine as long as the signs are appropriate on the facts.

So an accounting system would hold a mapping of low level account codes through a hierarchy to EBIT, similar to;

Level 1Level 2Level 3Level 4

EBIT

GROSS PROFITSALESeg Sales Income
EBITGROSS PROFITCOSTeg Rent
EBITRUNNING EXPENSESRUNNING EXPENSESeg Marketing
HEADCOUNTHEADCOUNT

HEADCOUNT

HEADCOUNT

So I would construct a table like the above with Level 4 linking through to your facts & use the Level 1-3 I the pivot table with summary totals where appropriate.

Cheers,

Chris.

samuelandlopes
Contributor

Re: personalised dimension

tks for info.

the user wanna like bellow:

is that possible?

chrismarlow
Valued Contributor

Re: personalised dimension

Hi Samuel,

If your user has very fixed ideas that this should look exactly like Excel then you may have a struggle on your hands , maybe they need Power Pivot .

Let me see if I can get close, but I will give up if it is fighting too hard.

Cheers,

Chris.

samuelandlopes
Contributor

Re: personalised dimension

kkk.yes Chris...i agree..tks 4 help.