Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
If you have QV12.1 or above... you can check out the attached qvw
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)
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')
thanks
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
=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
I got the qvw.
I have an error in Dimension
This part looks a problem: "(Code, (=Sum(Montant), Desc))) "
As pointed out, this will only work in QV12.1 and above...
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)