Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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)