1 Reply Latest reply: Sep 27, 2018 9:53 AM by Martijn Wanders

# Help with calculated row in pivot table QlikSense

Hi all,

A couple of days i'm struggling to add a calculated row in my pivot table. I read a lot on the Qlik forum but don't manage to implement something working. In the attachment you can find a screenshot. Below "Inkopen" and above "Omzet/reclame fee"I want a calculated row (where the red line is).

This is what I want to calculate:

(Omzet - Inkopen)/Omzet = Margin

So below "Inkopen" I want a new row with Margin and the result of the calculation above.

if(Dimensionality()=1, count(Sales), num(count({<Region={'Scotland'}>}Sales)/count({<Region={'England'}>}Sales),'#0%'))

If I want to use this for my challenge then:

if(Dimensionality()=4,

// The following is the used expression in first measure. It's count the revenue in current year (Boekjaar), specific accounts (grootboekrekening) and period.

num(-sum({<Boekjaar={\$(=max(Boekjaar))},[Grootboekrekening Soort]={'R'},Periode={"<=\$(=MAX(Periode))"}>}[Bedrag Saldo]),'#.##0'),

// The second part:

num((sum({<Mapping_N2={'Omzet'}>}Sales) - Sum({<Mapping_2={'Inkopen'} / sum({<Mapping_N2={'Omzet'}>}Sales) >})Sales),'#0%'))

Where "Sales" is red I don't know what to do? This is calculated with the same expression mention above. At the red mentioned sales there must be another set analysis I think.

Can somebody help me? Thank you in advance and would really appreciate it. More clarification? No problem and give a shout.

Greetings,

Martijn Wanders

• ###### Re: Help with calculated row in pivot table QlikSense

Hi Martijn,

see the pivot table as any other chart type and you'll get why

financial presentation of results in qlik is (in my opinion) not easy.

a way to achieve this is to create a pivot table with mutliple expressions (measures), that you will move from columns to row.

but this of course means you need to define everything. and that use of dimensions will be very limited.

the other way is to precalculate everything in your datamodel (again, will result in very little possible use of dimensions in your pivot/chart)

have a look at this extension: (bottom line: it requires a lot of data preparation); it will help you in some way

https://developer.qlik.com/garden/5703a1a86600bc2574ede248

• ###### Re: Help with calculated row in pivot table QlikSense

Hi Mikael,

Thank you for answer. I saw the extension and below was also a comment with a Youtube video URL. In this video the extension is explained and two other options.

This was very helpful and I'm going to look with my customer for the best solution! Have a nice day.