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: 
zarmoton
Creator
Creator

chart pivot table top 85% in cumulative value, and other ? Really good challenge ?? or easy ?

Hello

Who can help me please ?

in input i have those values:

 

LieuFamilleCodeMontant
ParisDupontA10
ParisDupontA20
ParisDupontB10
ParisDupontC20
ParisDupontC10
ParisDupontC20
ParisDupontD1
ParisDupontD2
ParisDupontD3
ParisDurandE5
ParisDurandE15
ParisDurandF50
ParisDurandF15
ParisDurandG10
ParisDurandG30
RouenZafH10
RouenZafI9
RouenZafI2
RouenZafK20

and i would like to have a pivot table, where i see the value to the top 85% (in cumulative), the rest in one raw "Other"

  

Keep only less than 85%, if more cumul in Other
LieuFamilleCodeMontantCumulative% Cumulative
ParisDupontC505052%
ParisDupontA308083%
ParisDupontOther1696100%
96
ParisDurandF656552%
ParisDurandG4010584%
ParisDurandOther20125100%
125
RouenZafK202049%
RouenZafI113176%
RouenZafOther1041100%
41

appreciate any help ?

10 Replies
sunny_talwar

If you have QV12.1 or above... you can check out the attached qvw

Capture.PNG

YoussefBelloum
Champion
Champion

Hi stalwar1

what is the trick here on the calculated dimension ?

My expression is this:

=RangeSum(Above( sum(Montant),0,RowNo()))/aggr(NODISTINCT sum( Montant),Famille)

why this on the calculated expression don't work ?

=aggr( if(RangeSum( Above(TOTAL   sum(Montant),0,RowNo()))/aggr(NODISTINCT sum( Montant),Famille)>0.83,'OTHER',Code),Code)

sunny_talwar

Because this isn't taking care of sorting by descending order of Sum(Montant).. where as this does

=If(Aggr(RangeSum(Above(Sum(Montant), 0, RowNo()))/Sum(TOTAL <Lieu, Famille> Montant), Lieu, Famille, (Code, (=Sum(Montant), Desc))) < 0.85, Code, 'Other')


Recipe for a Pareto Analysis – Revisited

YoussefBelloum
Champion
Champion

thanks

zarmoton
Creator
Creator
Author

excellent, thk you.

Excatly what i need

but i don't see the qvw in your answer

I'm with QV 11.2 , not 12.1

Can you please give me dimension and expression

zarmoton
Creator
Creator
Author

=If(Aggr(RangeSum(Above(Sum(Montant), 0, RowNo()))/Sum(TOTAL <Lieu, Famille> Montant), Lieu, Famille, (Code, (=Sum(Montant), Desc))) < 0.85, Code, 'Other')


this not work

zarmoton
Creator
Creator
Author

I got the qvw.

I have an error in Dimension

This part looks a problem: "(Code, (=Sum(Montant), Desc))) "

Capture.JPG

sunny_talwar

As pointed out, this will only work in QV12.1 and above...

zarmoton
Creator
Creator
Author

any idea how to do it with QV 11.2 ?

impossible to upgrade as i'm using QVExcel (an addon of Qlikview for Excel, working only to version 11.2)