Solved: Monto Acum, con varias dimensiones - Qlik Community

Report Inappropriate Content · ‎2016-06-20

Teniendo la siguiente tabla, cómo genero el campo MontoAcum con la venta acumulada por las dimeniones, COD, COM, Periodo y Tipo_Calculo

COD	ID_Com	Periodo	Tipo_Calculo	Sum(Monto)	Sum(MontoAcum)
2050000	100	201501	REAL	53.231.431	53.231.431
2050000	100	201502	REAL	36.847.236	90.078.667
2050000	100	201503	REAL	49.291.263	139.369.930
2050000	100	201504	REAL	36.906.095	176.276.025
2050000	100	201505	REAL	44.283.185	220.559.210
2050000	100	201506	REAL	39.180.921	259.740.131
2050000	100	201507	REAL	38.986.383	298.726.514
2050000	100	201508	REAL	29.908.047	328.634.561
2050000	100	201509	REAL	31.280.925	359.915.486
2050000	100	201510	REAL	29.469.506	389.384.992
2050000	100	201511	REAL	25.424.615	414.809.607
2050000	100	201512	REAL	30.220.063	445.029.670
2050000	200	201501	REAL	4.113.833	4.113.833
2050000	200	201502	REAL	8.327.617	12441450
2050000	200	201503	REAL	16.149.860
2050000	200	201504	REAL	20.647.885
2050000	200	201505	REAL	11.551.405
2050000	200	201506	REAL	12.993.380
2050000	200	201507	REAL	18.931.760
2050000	200	201508	REAL	12.016.472
2050000	200	201509	REAL	11.847.359
2050000	200	201510	REAL	10.664.742
2050000	200	201511	REAL	7.008.677
2050000	200	201512	REAL	11.715.126
2050000	300	201501	REAL	20.299.003
2050000	300	201502	REAL	17.851.789
2050000	300	201503	REAL	15.473.791
2050000	300	201504	REAL	14.984.270
2050000	300	201505	REAL	16.345.123
2050000	300	201506	REAL	13.991.289
2050000	300	201507	REAL	15.474.864
2050000	300	201508	REAL	10.366.742
2050000	300	201509	REAL	12.329.232
2050000	300	201510	REAL	11.070.763
2050000	300	201511	REAL	6.699.970
2050000	300	201512	REAL	9.256.347

Anonymous · ‎2016-06-21

Entonces lo mejor es usar previous y peek:

Datos:

LOAD

COD,

ID_Com,

Periodo,

Tipo_Calculo,

Num(Num#(Monto,'#.###',',','.')) as Monto;

LOAD * INLINE [

COD, ID_Com, Periodo, Tipo_Calculo, Monto

2050000, 100, 201501, REAL, 53.231.431

2050000, 100, 201502, REAL, 36.847.236

2050000, 100, 201503, REAL, 49.291.263

2050000, 100, 201504, REAL, 36.906.095

2050000, 100, 201505, REAL, 44.283.185

2050000, 100, 201506, REAL, 39.180.921

2050000, 100, 201507, REAL, 38.986.383

2050000, 100, 201508, REAL, 29.908.047

2050000, 100, 201509, REAL, 31.280.925

2050000, 100, 201510, REAL, 29.469.506

2050000, 100, 201511, REAL, 25.424.615

2050000, 100, 201512, REAL, 30.220.063

2050000, 200, 201501, REAL, 4.113.833

2050000, 200, 201502, REAL, 8.327.617

2050000, 200, 201503, REAL, 16.149.860

2050000, 200, 201504, REAL, 20.647.885

2050000, 200, 201505, REAL, 11.551.405

2050000, 200, 201506, REAL, 12.993.380

2050000, 200, 201507, REAL, 18.931.760

2050000, 200, 201508, REAL, 12.016.472

2050000, 200, 201509, REAL, 11.847.359

2050000, 200, 201510, REAL, 10.664.742

2050000, 200, 201511, REAL, 7.008.677

2050000, 200, 201512, REAL, 11.715.126

2050000, 300, 201501, REAL, 20.299.003

2050000, 300, 201502, REAL, 17.851.789

2050000, 300, 201503, REAL, 15.473.791

2050000, 300, 201504, REAL, 14.984.270

2050000, 300, 201505, REAL, 16.345.123

2050000, 300, 201506, REAL, 13.991.289

2050000, 300, 201507, REAL, 15.474.864

2050000, 300, 201508, REAL, 10.366.742

2050000, 300, 201509, REAL, 12.329.232

2050000, 300, 201510, REAL, 11.070.763

2050000, 300, 201511, REAL, 6.699.970

2050000, 300, 201512, REAL, 9.256.347

];

Final:

LOAD *,

IF(Previous(COD) = COD,

IF(Previous(ID_Com) = ID_Com,

RangeSum(Num(Monto),Peek('MontoAccum',0))

RangeSum(Num(Monto),Peek('MontoAccum',-1))

,Monto)

,Monto) as MontoAccum

RESIDENT Datos

ORDER BY COD ASC, ID_Com ASC, Periodo ASC;

Un saludo,

View solution in original post

JoaquinLazaro · ‎2016-06-21

Hola Antonio:

La expresión acumulada debe ser

RangeSum(Above(SUM( Monto ), 0, RowNo()))

Saludos

Joaquín

Anonymous · ‎2016-06-21

Hola Antonio,

¿Lo quieres calcular en Script o en el gráfico?

Gracias!

Report Inappropriate Content · ‎2016-06-21

Manuel, la idea es generarlo como un campo más

jolivares · ‎2016-06-21

Generarlo en el script no necesariamente es lo recomendale, ya que la belleza de QV es precisamente el poder realizar este tipo de calculos directamente en el diseno de tu dashboard.

Revisa la solucion que de Joaquin que entiendo te puede resolver tu problema.

Report Inappropriate Content · ‎2016-06-21

particularmente en este caso SI lo necesito como campo, he visto algunas soluciones creadas, pero no tengo la seguridad de como hacerlo con estos campos que ya están siendo utilizados

JoaquinLazaro · ‎2016-06-21

Hola Antonio:

Entonces no uses la función above() sino previous() dentro del load ... quiza debas ajustar la sintaxis, pero para eso estamos

Saludos

Joaquín

Anonymous · ‎2016-06-21

Entonces lo mejor es usar previous y peek:

Datos:

LOAD

COD,

ID_Com,

Periodo,

Tipo_Calculo,

Num(Num#(Monto,'#.###',',','.')) as Monto;

LOAD * INLINE [

COD, ID_Com, Periodo, Tipo_Calculo, Monto

2050000, 100, 201501, REAL, 53.231.431

2050000, 100, 201502, REAL, 36.847.236

2050000, 100, 201503, REAL, 49.291.263

2050000, 100, 201504, REAL, 36.906.095

2050000, 100, 201505, REAL, 44.283.185

2050000, 100, 201506, REAL, 39.180.921

2050000, 100, 201507, REAL, 38.986.383

2050000, 100, 201508, REAL, 29.908.047

2050000, 100, 201509, REAL, 31.280.925

2050000, 100, 201510, REAL, 29.469.506

2050000, 100, 201511, REAL, 25.424.615

2050000, 100, 201512, REAL, 30.220.063

2050000, 200, 201501, REAL, 4.113.833

2050000, 200, 201502, REAL, 8.327.617

2050000, 200, 201503, REAL, 16.149.860

2050000, 200, 201504, REAL, 20.647.885

2050000, 200, 201505, REAL, 11.551.405

2050000, 200, 201506, REAL, 12.993.380

2050000, 200, 201507, REAL, 18.931.760

2050000, 200, 201508, REAL, 12.016.472

2050000, 200, 201509, REAL, 11.847.359

2050000, 200, 201510, REAL, 10.664.742

2050000, 200, 201511, REAL, 7.008.677

2050000, 200, 201512, REAL, 11.715.126

2050000, 300, 201501, REAL, 20.299.003

2050000, 300, 201502, REAL, 17.851.789

2050000, 300, 201503, REAL, 15.473.791

2050000, 300, 201504, REAL, 14.984.270

2050000, 300, 201505, REAL, 16.345.123

2050000, 300, 201506, REAL, 13.991.289

2050000, 300, 201507, REAL, 15.474.864

2050000, 300, 201508, REAL, 10.366.742

2050000, 300, 201509, REAL, 12.329.232

2050000, 300, 201510, REAL, 11.070.763

2050000, 300, 201511, REAL, 6.699.970

2050000, 300, 201512, REAL, 9.256.347

];

Final:

LOAD *,

IF(Previous(COD) = COD,

IF(Previous(ID_Com) = ID_Com,

RangeSum(Num(Monto),Peek('MontoAccum',0))

RangeSum(Num(Monto),Peek('MontoAccum',-1))

,Monto)

,Monto) as MontoAccum

RESIDENT Datos

ORDER BY COD ASC, ID_Com ASC, Periodo ASC;

Un saludo,

Report Inappropriate Content · ‎2016-06-21

MUCHAS GRACIAS, para el caso donde el tipo_calculo no sea solamente REAL, también contenga PPTO, la formula que me quedó fue

Datos:

LOAD

COD,

ID_Com,

Periodo,

Tipo_Calculo,

Num(Num#(Monto,'#.###',',','.')) as Monto;

LOAD * INLINE [

COD, ID_Com, Periodo, Tipo_Calculo, Monto

2050000, 100, 201501, REAL, 53.231.431

2050000, 100, 201502, REAL, 36.847.236

2050000, 100, 201503, REAL, 49.291.263

2050000, 100, 201504, REAL, 36.906.095

2050000, 100, 201505, REAL, 44.283.185

2050000, 100, 201506, REAL, 39.180.921

2050000, 100, 201507, REAL, 38.986.383

2050000, 100, 201508, REAL, 29.908.047

2050000, 100, 201509, REAL, 31.280.925

2050000, 100, 201510, REAL, 29.469.506

2050000, 100, 201511, REAL, 25.424.615

2050000, 100, 201512, REAL, 30.220.063

2050000, 200, 201501, REAL, 4.113.833

2050000, 200, 201502, REAL, 8.327.617

2050000, 200, 201503, REAL, 16.149.860

2050000, 200, 201504, REAL, 20.647.885

2050000, 200, 201505, REAL, 11.551.405

2050000, 200, 201506, REAL, 12.993.380

2050000, 200, 201507, REAL, 18.931.760

2050000, 200, 201508, REAL, 12.016.472

2050000, 200, 201509, REAL, 11.847.359

2050000, 200, 201510, REAL, 10.664.742

2050000, 200, 201511, REAL, 7.008.677

2050000, 200, 201512, REAL, 11.715.126

2050000, 300, 201501, REAL, 20.299.003

2050000, 300, 201502, REAL, 17.851.789

2050000, 300, 201503, REAL, 15.473.791

2050000, 300, 201504, REAL, 14.984.270

2050000, 300, 201505, REAL, 16.345.123

2050000, 300, 201506, REAL, 13.991.289

2050000, 300, 201507, REAL, 15.474.864

2050000, 300, 201508, REAL, 10.366.742

2050000, 300, 201509, REAL, 12.329.232

2050000, 300, 201510, REAL, 11.070.763

2050000, 300, 201511, REAL, 6.699.970

2050000, 300, 201512, REAL, 9.256.347

2050000, 300, 201510, PPTO, 10

2050000, 300, 201511, PPTO, 4

2050000, 300, 201512, PPTO, 6

];

Final:

LOAD *,

IF(Previous(COD) = COD,

IF(Previous(ID_Com) = ID_Com,

IF(Previous(Tipo_Calculo) = Tipo_Calculo,

RangeSum(Num(Monto),Peek('MontoAccum',-1))

,Monto)

,Monto) as MontoAccum

RESIDENT Datos

ORDER BY COD ASC, ID_Com ASC, Tipo_Calculo ASC, Periodo ASC;

EXIT Script;

Anonymous · ‎2016-06-21

Genial,

Me alegro de que te haya resultado útil.

Un saludo!