# 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
Did you mean:
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:

 Lieu Famille Code Montant Paris Dupont A 10 Paris Dupont A 20 Paris Dupont B 10 Paris Dupont C 20 Paris Dupont C 10 Paris Dupont C 20 Paris Dupont D 1 Paris Dupont D 2 Paris Dupont D 3 Paris Durand E 5 Paris Durand E 15 Paris Durand F 50 Paris Durand F 15 Paris Durand G 10 Paris Durand G 30 Rouen Zaf H 10 Rouen Zaf I 9 Rouen Zaf I 2 Rouen Zaf K 20

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 Lieu Famille Code Montant Cumulative % Cumulative Paris Dupont C 50 50 52% Paris Dupont A 30 80 83% Paris Dupont Other 16 96 100% 96 Paris Durand F 65 65 52% Paris Durand G 40 105 84% Paris Durand Other 20 125 100% 125 Rouen Zaf K 20 20 49% Rouen Zaf I 11 31 76% Rouen Zaf Other 10 41 100% 41

appreciate any help ?

10 Replies
MVP

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

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)

MVP

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

Champion

thanks

Creator
Author

excellent, thk you.

Excatly what i need

I'm with QV 11.2 , not 12.1

Can you please give me dimension and expression

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

Creator
Author

I got the qvw.

I have an error in Dimension

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

MVP

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

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)