Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good afternoon!
In a "pivot table" I wish to present the percentage of participation. I want to get:
| Canal | Customer | Charged | % Charged |
|---|---|---|---|
| ZZ | TOTAL | 30.00 | 100.00 % |
| CONS | 10.00 | 10.00% | |
| DKKK | 0.00 | 0.00% | |
| YYYY | 20.00 | 20.00% | |
| 11 | TOTAL | 50.00 | 100.00% |
| ALLL | 15.00 | 30.00% | |
| FGGG | 35.00 | 70.00% |
To display all channels and customers, the properties' Show All Values "is marked, 'Suppress When Value Is Null" (in the Dimensions Tab) and demarcated' Suppress Zero-Values' and 'Suppress Missing' from the 'pivot table '. This allows me to present all the channels and customers even if its value is zero.
The formulas used are:
- Charged : Sum (Collection)
- % Charged : Sum (Charges) / Aggr (NODISTINCT Sum ([Collection]), [Channel])
However, profit not present the '% Charged'; therefore, I request your help and suggestions. Instead of getting, as presented in the above table, not% for Channel 'ZZ' and Client 'DKKK' occurs. In other cases, although there are a number, it does not perform the division.
| Canal | Customer | Charged | % Charged |
|---|---|---|---|
| ZZ | TOTAL | 30.00 | 100.00 % |
| CONS | 10.00 | 10.00% | |
| DKKK | 0.00 | ||
| YYYY | 20.00 | 20.00% | |
| 11 | TOTAL | 50.00 | 100.00% |
| ALLL | 15.00 | 30.00% | |
| FGGG | 35.00 | 70.00% |
I noticed that Aggr (NODISTINCT Sum ([Collection]), [Channel]) does not recover value (which is 30.00) so that the division is not carried out. Attached a picture with more detail about the problem described.
In the picture above:
- The formula for divisor1 is: Aggr (NODISTINCT Sum ([Collection]), [Channel])
- There cumulative values for Channel 'ZZ' and Chain 'CM00', for example, in May 2015 but in June 2015 their amounts are equal to 0.00. The accumulated% participation (second column labeled%) should be: 0.0048% (= 7,920.00 * 100 / 165,170,437.28) and 0.0015% (2,520.00 * 100 / 165,170,437.28); however, they not presented.
I appreciate your help and suggestions.
Sincerely,
--- Miguel Angel
Translated with Google Translate - Qlik Community Administrative Team
¡Buenas tardes!
En una 'pivot table' deseo presentar el porcentaje de participación. Deseo obtener:
| Canal | Cliente | Cobrado | % Cobrado |
|---|---|---|---|
| ZZ | TOTAL | 30.00 | 100.00 % |
| CONS | 10.00 | 10.00% | |
| DKKK | 0.00 | 0.00% | |
| VVVV | 20.00 | 20.00% | |
| 11 | TOTAL | 50.00 | 100.00% |
| ALLL | 15.00 | 30.00% | |
| FGGG | 35.00 | 70.00% |
Para presentar todos los canales y clientes, se marcaron las propiedades 'Show All Values', 'Suppress When Value Is Null' (en el Tab Dimensions) así como se demarcaron 'Suppress Zero-Values' y 'Suppress Missing' de la 'pivot table'. Esto me permite presentar todos los canales y los clientes aún si su valor es cero.
Las fórmulas utilizadas son:
- Cobrado: Sum(Cobros)
- % Cobrado: Sum(Cobros) / Aggr(NODISTINCT Sum([Cobros]), [Canal])
Sin embargo, no logro presentar el '% Cobrado'; por lo mismo, solicito su ayuda y sugerencias. En lugar de obtener, lo presentado en la tabla anterior, no se presenta % para el Canal 'ZZ' y Cliente 'DKKK'. En otros casos, aunque la existe una cantidad, no realiza la división.
| Canal | Cliente | Cobrado | % Cobrado |
|---|---|---|---|
| ZZ | TOTAL | 30.00 | 100.00 % |
| CONS | 10.00 | 10.00% | |
| DKKK | 0.00 | ||
| VVVV | 20.00 | 20.00% | |
| 11 | TOTAL | 50.00 | 100.00% |
| ALLL | 15.00 | 30.00% | |
| FGGG | 35.00 | 70.00% |
Observé que Aggr(NODISTINCT Sum([Cobros]), [Canal]) no recupera valor (que es 30.00) por lo que la división no se lleva a cabo.
Adjunto una imagen con mayor detalle sobre el problema descrito.
De la imagen anterior:
- La fórmula para divisor1 es: Aggr(NODISTINCT Sum([Cobros]), [Canal])
- Existen valora para los acumulados del Canal 'ZZ' y Cadena 'CM00', por ejemplo, en Mayo 2015 aunque en Junio 2015 sus sumas son iguales a 0.00. El % participación para los acumulados (segunda columna etiquetada con %) debería ser: 0.0048 % (=7,920.00*100/165,170,437.28) y 0.0015 %(2,520.00*100/165,170,437.28); sin embargo, no se presentan.
Agradezco de antemano su ayuda y sus sugerencias.
Cordialmente,
--- Miguel Angel