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.
I read about dimensionality function. I found this formula which add a row with the result:
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
