Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Subtracting rows in pivot table

Hi everyone,

I was about to create a sort of "income statement" (profit and loss) in qlik Sense. I tried the extension P&L Smart, but it is actually too limited for what I meant to do. A pivot table, instead, might suit better. However, before starting, I wanted to know whether it is possible to subtract two specific rows in a pivot table, and if yes, how.

I know it's possible to create a P6L Statement in View, but I don't find any material related to Sense.

e.g. I have

Operating revenues

Operating expenses.

I want to create a new row, say, Gross Profit, that is "Operating revenues"-"Operating expenses".

Thanks in advance.

1 Solution

Accepted Solutions
Not applicable

Re: Subtracting rows in pivot table

Thanks for your help. Anyway, I finally managed to follow this guide

https://community.qlik.com/docs/DOC-3324

and adapt everything to QlikSense.

After some work, here's the result. (please see attachment).

10 Replies
beck110979
Valued Contributor III

Re: Subtracting rows in pivot table

Hi Gabriele,

if you already tried to work with extension: P&L Smart, in this extension you use only 2 diimension, you can use for your issue: the straighttable, please check this web-page: https://www.vizlib.com/table/#4whatsnew

or you give me an example screenshot and we will find another solution

i hope that helps

Beck

Not applicable

Re: Subtracting rows in pivot table

I need exactly what a pivot table does in Qlik sense, i.e., the possibility of expanding fields.

However, a need also to subtract rows, and that's not easy to do. Does that extension do the job?

beck110979
Valued Contributor III

Re: Subtracting rows in pivot table

Hi Gabriele,

can you give me an example screenshot of your expected output

OmarBenSalem
Esteemed Contributor

Re: Subtracting rows in pivot table

Can you show us what you have? (an image)

the measures you're using and you want to do?

It would be much easier to help you that way

Not applicable

Re: Subtracting rows in pivot table

Sure, sorry but I thought somebody already tried to replicate a profit and loss statement before

I actually need something like this. With, let's say, Net earnings being the difference between "Earnings before income and taxes" and "Gross profit" (that's not economiccaly right, but it's just an example to show what I needed to do).

Thanks

Immagine.png

Not applicable

Re: Subtracting rows in pivot table

please see below the answer to omar. thanks

beck110979
Valued Contributor III

Re: Subtracting rows in pivot table

Hi Gabriele,

what do you think about it?

Suggestion 1.PNG

Beck

OmarBenSalem
Esteemed Contributor

Re: Subtracting rows in pivot table

Sure you can;

1)Activate the total in your first dimension

2) suppose your expression is : sum(sales)

replace it like this:

if(dimensionality()=0,sum({Dim1={'Earning before incomes'}Sales)-sum({'GROSS PROFIT'}Sales),

sum(Sales))

per analogy:

I have in the below table:

- 2 dimensions: OICA region and territory code:

1) I activate the total in the first dimension :

Capture.PNG

2) then altered my measure as follow:

if(Dimensionality()=0,Sum({<[OICA region]={'AOME','Americas'}>}[Commercial vehicle production]),

sum([Commercial vehicle production]))


ps: dimensionality()=1 is the first dimension

dimensionality()=2 is the second dimension and so on

dimensionality()=0 is the total


so with my measure; for the total row I called Aome+americas, I want to have the car production for aome+americas.

Capture.PNG

You have the idea, alter your expression to answer your question


hope this helps

beck110979
Valued Contributor III

Re: Subtracting rows in pivot table

Hi Omar,

How are you, i hope you are well,

it does look great, do you have an qvf-example?

Thanks a lot

Beck

Community Browser