Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calcul d'une contribution Positive & Négative

Bonjour à tous les Qlikeur

Je viens vers vous car j'ai du mal à calculer dans un tableau la contribution positive et negative sur des categories de produits.

Ci-dessous un exemple de data

[Product]:

Load * Inline [

Catégorie, Sous Catégorie, CA N, CA N-1

A, A1, 150, 180

A, A2, 230, 110

A, A3, 75, 95

A, A4, 210, 165

B, B1, 65, 10

B, B2, 135, 145

B, B3, 295,210

B, B4, 50, 75

C, C1, 35, 210

C, C2, 45, 15

C, C3, 245, 250

C, C4, 150, 100

];

Ci-dessous le résultat que je souhaite obtenir

   

CatégorieCA N Contribution Part de contribution
A66511552%
B54510548%
C475-100-100%

A l'heure actuelle je n'arrive pas à faire mon calcul uniquement sur les contribution Positives ou Négatives.

J'utilise aussi une dimension hierarchique, donc si on clique sur la catégorie A on doit obtenir le résultat suivant

   

CatégorieCA N Contribution Part de contribution
A1150-30-60%
A223012073%
A375-20-40%
A42104527%

Je reste diso si vous avez la moindre question.

Merci d'avance pour vos réponse

1 Solution

Accepted Solutions
sunny_talwar

Not sure how to get the dimension in the Aggr() function, but when you have Catégorie as dimension, this would work...

=(Sum([CA N]) - Sum([CA N-1]))/

fabs(If(Sum([CA N]) - Sum([CA N-1]) > 0, Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) > 0, Sum([CA N]) - Sum([CA N-1])), $(='[' & GetCurrentField(Group) & ']'))), Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) < 0, Sum([CA N]) - Sum([CA N-1])), Catégorie))))

When Sous Catégorie is the dimension... then this would work

=(Sum([CA N]) - Sum([CA N-1]))/

fabs(If(Sum([CA N]) - Sum([CA N-1]) > 0, Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) > 0, Sum([CA N]) - Sum([CA N-1])), $(='[' & GetCurrentField(Group) & ']'))), Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) < 0, Sum([CA N]) - Sum([CA N-1])), [Sous Catégorie]))))

Now may be you can use a if statement or another way to handle this, but the idea is to use your current chart dimension in the Aggr()

View solution in original post

8 Replies
sunny_talwar

Try this

=(Sum([CA N]) - Sum([CA N-1]))/

fabs(If(Sum([CA N]) - Sum([CA N-1]) > 0, Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) > 0, Sum([CA N]) - Sum([CA N-1])), $(='[' & GetCurrentField(Group) & ']'))), Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) < 0, Sum([CA N]) - Sum([CA N-1])), $(='[' & GetCurrentField(Group) & ']')))))

Capture.PNG

Capture.PNG

Anonymous
Not applicable
Author

Thanks for your answer, but I'm on Qlik Sense, and the formula returns no results.

What the $(='[' & GetCurrentField(Group) & ']') in the formula, works with Qlik Sense?

Sorry for my poor English

sunny_talwar

Oh okay... QlikSense don't have GetCurrentField function.... I know it does have DrillDown Group... but not sure how to refer to them in the Aggr() function... let me do some research to see if I can find out....

Just to make sure, you are using (or planning to use) a drilldown group right?

Anonymous
Not applicable
Author

Yes, I'm using a drilldown group

sunny_talwar

Not sure how to get the dimension in the Aggr() function, but when you have Catégorie as dimension, this would work...

=(Sum([CA N]) - Sum([CA N-1]))/

fabs(If(Sum([CA N]) - Sum([CA N-1]) > 0, Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) > 0, Sum([CA N]) - Sum([CA N-1])), $(='[' & GetCurrentField(Group) & ']'))), Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) < 0, Sum([CA N]) - Sum([CA N-1])), Catégorie))))

When Sous Catégorie is the dimension... then this would work

=(Sum([CA N]) - Sum([CA N-1]))/

fabs(If(Sum([CA N]) - Sum([CA N-1]) > 0, Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) > 0, Sum([CA N]) - Sum([CA N-1])), $(='[' & GetCurrentField(Group) & ']'))), Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) < 0, Sum([CA N]) - Sum([CA N-1])), [Sous Catégorie]))))

Now may be you can use a if statement or another way to handle this, but the idea is to use your current chart dimension in the Aggr()

Anonymous
Not applicable
Author

It's ok with this formula

If(GetSelectedCount(Catégorie)=1,

(Sum([CA N]) - Sum([CA N-1]))/
fabs(If(Sum([CA N]) - Sum([CA N-1]) > 0,
Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) > 0,
Sum([CA N]) - Sum([CA N-1])),
[Sous Catégorie])),
Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) < 0,
Sum([CA N]) - Sum([CA N-1])),
[Sous Catégorie])))),

(Sum([CA N]) - Sum([CA N-1]))/
fabs(If(Sum([CA N]) - Sum([CA N-1]) > 0,
Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) > 0,
Sum([CA N]) - Sum([CA N-1])),
Catégorie)),
Sum(TOTAL Aggr(If(Sum([CA N]) - Sum([CA N-1]) < 0,
Sum([CA N]) - Sum([CA N-1])),
Catégorie))))
)

What do u think about "GetSelectedCount" ?

Thx a lot for your answer

sunny_talwar

I think this is smart.... Didn't think about this

Anonymous
Not applicable
Author

Ok cool, I tell you again thx a lot for your answer