Dynamix % per dimension in a pivot table

[gerald_lebret]

HI,

I have havin an issue with a calculation in Qlik Sense.

I created a pivot table that looks like the example below for which I used random figures:

All my values are calculated using alternate states and set analysis.

This is, for example my formula for my revenue:

-SUM({Altst_CA< Succursale=$::Succursale,
[ACCOUNTS.Compte] = $::[ACCOUNTS.Compte],
[ENTRY.Type_ecriture]=$::[ENTRY.Type_ecriture],
[CENTRES.Centre]=$::[CENTRES.Centre],
[NATURE.Nature]=$::[NATURE.Nature],
[AXE.Axe]=$::[AXE.Axe],
[CAL.Annee_fiscale]={'$(=vAnneeN)'},
[CAL.Periode]=$::[CAL.Periode],
[CAL.Mois]={"<=$(=vSelectMois)"},
[CAL.Mois_texte] = $::[CAL.Mois_texte],
[CAL.Annee_mois]=$::[CAL.Annee_mois],
[ENTRY.Categorie_ecriture]={'Réel'},
[ENTRY.Periode_type] = {'Normal'}
>} [ENTRY.Montant_base]
)

Now I need to calculate the % for each expense account (including the totals and subtotals) for each CC.

Hence, here is the result I need:

I can't get to the result I need.

I even tryed to created a variable to calculate the revenue value but my variable is not dynamic and I ended looking at % that are calculated over the total revenue and not per cost center.

Can anyone help?

Thanks

Gerald

[Rohan]

Hi,

Can you post your % Expression ?

I think you need to use total<CostCentre> in your denominator.

For further explanation, please check out the following link :

https://community.qlik.com/t5/QlikView-App-Dev/Keywords-TOTAL-ALL-DISTINCT/td-p/143514

Thanks & Regards,

Rohan.

[gerald_lebret]

Hello,

Here is my % expression:

if([ENTRY.Comparatif_groupe_1] = 'CA','',

(SUM({Altst_Salaires< Succursale=$::Succursale,

[ACCOUNTS.Compte] = $::[ACCOUNTS.Compte],

                [ENTRY.Type_ecriture]=$::[ENTRY.Type_ecriture],

                [CENTRES.Centre]=$::[CENTRES.Centre],

                [NATURE.Nature]=$::[NATURE.Nature],

                [AXE.Axe]=$::[AXE.Axe],

                [CAL.Annee_fiscale]={'$(=vAnneeN)'},

                [CAL.Periode]=$::[CAL.Periode],

                [CAL.Mois]={"<=$(=vSelectMois)"},

                [CAL.Mois_texte] = $::[CAL.Mois_texte],

                [CAL.Annee_mois]=$::[CAL.Annee_mois],

                [ENTRY.Periode_type] = {'Normal'},

                        [ENTRY.Categorie_ecriture]={'Réel'}

>} [ENTRY.Montant_base]

    )

+

SUM({Alt_charges_expl< Succursale=$::Succursale,

[ACCOUNTS.Compte] = $::[ACCOUNTS.Compte],

                [ENTRY.Type_ecriture]=$::[ENTRY.Type_ecriture],

                [CENTRES.Centre]=$::[CENTRES.Centre],

                [NATURE.Nature]=$::[NATURE.Nature],

                [AXE.Axe]=$::[AXE.Axe],

                [CAL.Annee_fiscale]={'$(=vAnneeN)'},

                [CAL.Periode]=$::[CAL.Periode],

                [CAL.Mois]={"<=$(=vSelectMois)"},

                [CAL.Mois_texte] = $::[CAL.Mois_texte],

                [CAL.Annee_mois]=$::[CAL.Annee_mois],

                [ENTRY.Periode_type] = {'Normal'},

                [ENTRY.Categorie_ecriture]={'Réel'}

>} [ENTRY.Montant_base]

    )

)

/

-SUM(total<[CENTRES.Centre]>{Altst_CA< Succursale=$::Succursale,

[ACCOUNTS.Compte] = $::[ACCOUNTS.Compte],

                [ENTRY.Type_ecriture]=$::[ENTRY.Type_ecriture],

                [CENTRES.Centre]=$::[CENTRES.Centre],

                [NATURE.Nature]=$::[NATURE.Nature],

                [AXE.Axe]=$::[AXE.Axe],

                [CAL.Annee_fiscale]={'$(=vAnneeN)'},

                [CAL.Periode]=$::[CAL.Periode],

                [CAL.Mois]={"<=$(=vSelectMois)"},

                [CAL.Mois_texte] = $::[CAL.Mois_texte],

                [CAL.Annee_mois]=$::[CAL.Annee_mois],

                [ENTRY.Categorie_ecriture]={'Réel'},

                [ENTRY.Periode_type] = {'Normal'}

>} [ENTRY.Montant_base]

    )

)

As you can see, I did add the total <CENTRES.Centre> but it doesn't work.

Thank you.

Thanks

[Rohan]

Hi,

It seems your expression is not executing the else part of your if at all, kindly check the if condition once & replace ' ' & your % expression with 1 & 2 & confirm on this.

Thanks & Regards,

Rohan.