topic Re: Hellow Experts I need Help in Set analysis in QlikView

Hellow Experts I need Help in Set analysis

Mon, 23 Apr 2012 16:06:56 GMT

I Have the colums sum of total sales for each months and the acumalte of this sales for divison.

I need to show the % of this total sales / sales acumulates for division.

How I do this without show the columns (total sales and acumaltes sales for division) in the pivot table, I need to make the expression in the set analysis.

Mes	Ventas Totales	acumulado lujo
Ene	264,314.96	88,400.73
Feb	1,150,807.67	558,189.03
Mar	1,753,029.63	435,252.20
Total	1,753,029.63	1,081,841.96

Thank alot.

Re: Hellow Experts I need Help in Set analysis

nilesh_gangurde — Mon, 23 Apr 2012 17:09:31 GMT

Hi Mario ,

PFA and there are two types to write the expresion to calculate the accumulated value for pivot.

If possible attach the sample qvw file so I can help you.

Regards,

Nilesh Gangurde

Re: Hellow Experts I need Help in Set analysis

Mon, 23 Apr 2012 17:35:59 GMT

Thanks Alot Nilesh.

How do I Attach the qvw File.

I need to calculate the result of last two columns in the next excel view.

Participacion Lujo Acum = Ventas Totales Acumuladas / Acumulado Lujo.

Participacion Masivo Acum = Ventas Totales Acumuladas / Acumulado Masivo. I need make those calculates but without show the colums (Ventas Totales Acumuladas, Acumulado Lujo, Acumulado Masivo).

Mes

Ventas Totales Mensuales

Ventas Totales Acumuladas

Ventas Lujo

Ventas Masivo

Acumulado lujo

Acumulado Masivo

Participación Lujo

Participación Masivo

Participación Lujo Acum

Participación Masivo Acum

Ene

253,879.52

83,862.78

170,016.74

83,862.78

170,016.74

33.03%

66.97%

33.03%

66.97%

Feb

807,086.27

1060,965.79

457,689.76

349,396.51

541,552.54

519,413.25

56.71%

43.29%

51.04%

48.96%

Mar

782,214.67

1843,180.46

415,396.31

366,818.36

956,948.85

886,231.61

53.11%

46.89%

51.92%

48.08%

Abr

872,548.86

2715,729.32

526,446.46

346,102.40

1483,395.31

1232,334.01

60.33%

39.67%

54.62%

45.38%

May

842,832.43

3558,561.75

431,164.33

411,668.10

1914,559.64

1644,002.11

51.16%

48.84%

53.80%

46.20%

Jun

952,822.32

4511,384.07

535,246.43

417,575.89

2449,806.07

2061,578.01

56.17%

43.83%

54.30%

45.70%

Jul

752,102.50

5263,486.58

444,723.95

307,378.55

2894,530.02

2368,956.56

59.13%

40.87%

54.99%

45.01%

Ago

768,761.87

6032,248.44

383,423.46

385,338.41

3277,953.47

2754,294.97

49.88%

50.12%

54.34%

45.66%

Sep

790,272.95

6822,521.39

434,334.48

355,938.47

3712,287.95

3110,233.44

54.96%

45.04%

54.41%

45.59%

Oct

976,955.96

7799,477.35

649,204.59

327,751.37

4361,492.54

3437,984.80

66.45%

33.55%

55.92%

44.08%

Nov

1686,019.93

9485,497.28

1262,485.36

423,534.57

5623,977.90

3861,519.38

74.88%

25.12%

59.29%

40.71%

Dic

798,518.53

10284,015.81

353,375.49

445,143.04

5977,353.39

4306,662.42

44.25%

55.75%

58.12%

41.88%

Total

10284,015.81

5977,353.39

4306,662.42

5977,353.39

4306,662.42

58.12%

41.88%

58.12%

41.88%

Thank. For you Help

Saludos

Mario Diaz Parham

Jefe Informática

Grupo Midence Soto Pierrefeu

Tel. (504) 2280 32 57

De: Nilesh Gangurde

Enviado el: Lunes, 23 de Abril de 2012 11:10 a.m.

Para: Mario Diaz Parham

Asunto: - Re: Hellow Experts I need Help in Set analysis

<http://community.qlik.com/index.jspa> QlikCommunity

Re: Hellow Experts I need Help in Set analysis

created by Nilesh Gangurde <http://community.qlik.com/people/nilesh.gangurde> in Development (QlikView Desktop) - View the full discussion <http://community.qlik.com/message/212887#212887

Re: Hellow Experts I need Help in Set analysis

chematos — Mon, 23 Apr 2012 18:59:56 GMT

Hola Mario,

Creo que para sacar el porcentaje la división es al revés pero bueno, la cuestión es que para sumar los totales puedes usar All cuando haces la suma. El valor será fijo puesto que es la suma total, sin hacer caso de los filtros.

Sum(all [acumulado lujo] )/sum(all [Ventas Totales])

Si lo he entendido bien, espero que te sirva.

Saludos,

Chema

Re: Hellow Experts I need Help in Set analysis

Mon, 23 Apr 2012 19:21:43 GMT

Muchas gracias José.

Lo que pasa es que tanto el total de ventas, como el total acumulado de lujo, deben ser valores acumulados por línea. Ejem.

Mes Venta Total Mensual Total Venta Acum Venta mensual Lujo Total Venta acum lujo

Ene 100 100 65 65

Feb 250 350 200 265

Mar 300 650 150 415

Entonces debo dividir mi total venta Acum Lujo / total venta acum, pero no debo mostrar las columnas anteriores el pivot table solo debe mostrar la columna de %.

He probado con la función RangeSum pero todavía no tengo un resultado correcto.

Muchas gracias de antemano.

Saludos

Mario Diaz Parham

Jefe Informática

Grupo Midence Soto Pierrefeu

Tel. (504) 2280 32 57

De: José Mª Tos

Enviado el: Lunes, 23 de Abril de 2012 01:00 p.m.

Para: Mario Diaz Parham

Asunto: - Re: Hellow Experts I need Help in Set analysis

<http://community.qlik.com/index.jspa> QlikCommunity

Re: Hellow Experts I need Help in Set analysis

created by José Mª Tos <http://community.qlik.com/people/chema.tos> in Development (QlikView Desktop) - View the full discussion <http://community.qlik.com/message/212944#212944