topic Re: HELP!! in QlikView

HELP!!

Wed, 25 Nov 2020 16:16:04 GMT

Buen dia.

Les escribo para que me puedan ayudar con esta herramienta ya que soy nuevo en esto,

quería realizar una tabla que en excel se veria asi:

Imagen 1:

PRODUCTOS	ENERO	FEBRERO	MARZO	VENTAS	PROMEDIO
A	100	200	50	350	117
B	200	10	20	230	77
C	50	0	100	150	50
D

cuando lo hago en la herramienta Qlikview me aparece asi:

Imagen 2:

PRODUCTOS	ENERO	ENERO	FEBRERO	FEBRERO	MARZO	MARZO	VENTA	PROMEDIO
	VENTA	PROMEDIO	VENTA	PROMEDIO	VENTA	PROMEDIO
A	100	3	50	17	200	67	350	117
B	200	37	20	7	10	4	230	77
C	50	17	100	150	0	0	150	50

Como puedo hacer que la columna promedio me quede como la imagen 1:

Gracias por su ayuda.

Re: HELP!!

vishsaggi — Tue, 15 Nov 2016 20:13:19 GMT

Can you translate this to English and let us know little more information on how the yellow highlighted values were derived?

Thanks,
V.

Re: HELP!!

Tue, 15 Nov 2016 21:37:38 GMT

Good day.

I am writing to you so that you can help me with this tool since I am new to this,

I wanted to make this table that in microsoft excel would look like this:

Imagen 1:

PRODUCT	ENE	FEBR	MAR	SALES	AVERAGE
A	100	200	50	350	117
B	200	10	20	230	77
C	50	0	100	150	50

When I create the table in QlikView it is shown to me like this:

Imagen 2:

PRODUCT	ENERO	ENERO	FEBRERO	FEBRERO	MARZO	MARZO	SALES	AVERAGE
	SALES	AVERAGE	SALES	AVERAGE	SALES	AVERAGE
A	100	3	50	17	200	67	350	117
B	200	37	20	7	10	4	230	77
C	50	17	100	150	0	0	150	50

How can i create table as image 1 in Qlikview?

The yellow column is the average sales. for example:

It is the total sum of sales between the months that is carried in the year,

Thanks for your help.

Re: HELP!!

MarcoWedel — Tue, 15 Nov 2016 22:24:17 GMT

please post a sample qvw to work with

thanks

regards

Marco

Re: HELP!!

vishsaggi — Tue, 15 Nov 2016 23:09:38 GMT

Can we see your script, To what i understood you want to display table1 in your Qlikview when pulling data from excel as shown in Image2? Correct me?

Can you load this and see if this is what you are looking for? Change your excel sheet path accordingly.

IF you are using a personal edition and cant open the file, Try below:

Load your excel sheet with these columns only:

LOAD PRODUCT,

ENERO,

FEBRERO,

MARZO

FROM

<Giveyour excel path here\Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Add a straight table chart and add

Dimensions: PRODUCT and supress null value for this.

Expressions:

1. sum(ENERO)

2. Sum(FEBRERO)

3. SUm(MARZO)

4. Sum(ENERO) + Sum(FEBRERO) + Sum(MARZO)

5. Sum(ENERO+FEBRERO+MARZO)/Count(TOTAL PRODUCT)

In the number tab change the expression default for Average expression to Integer value.

Re: HELP!!

Tue, 15 Nov 2016 23:26:25 GMT

What I want to show in Qlikview is image 1.

That the average only appears to me only once, not every month.

Re: HELP!!

Anil_Babu_Samineni — Wed, 16 Nov 2016 00:44:14 GMT

As you said, you are expecting image 1 in Qlikview.

So, can you share the formula of average for image 1 which showing in cell. I am asking you to share the cells expression / formula

Re: HELP!!

varshavig12 — Wed, 16 Nov 2016 04:14:14 GMT

I got something like this,

Now, what I understood is you want to see the sum of each Product per month, its total sum , its total avg.

And what you don't want to see is avg of each product per month.

Right ?

Re: HELP!!

MayilVahanan — Wed, 16 Nov 2016 11:08:00 GMT

Try like attached file

PRODUCT	Sales	Average	ENE	FEBR	MAR
A	350	116.66667	100	200	50
B	230	76.666667	200	10	20
C	150	50	50	-	100

Hope it helps

Re: HELP!!

vishsaggi — Wed, 16 Nov 2016 14:22:47 GMT

Can you share your expected output with some sample data?

Re: HELP!!

Wed, 16 Nov 2016 15:06:48 GMT

Right.

As you want to display the data in the QlikVies dashboard is like this:

Month	ENE	FEBR	MAR	Total	Total
Product	Sum(Value)	Sum(Valu)	Sum(Value)	Sum(Value)	Avg(Value)
A	100	200	200	500	167
B	200	10	230	440	147
C	50	0	150	200	67

Re: HELP!!

Anil_Babu_Samineni — Wed, 16 Nov 2016 15:18:03 GMT

How about this? Attached the Work file

Updated: Attachment

Re: HELP!!

Wed, 16 Nov 2016 15:23:07 GMT

The attachment does not appear

Re: HELP!!

Wed, 16 Nov 2016 15:23:17 GMT

The attachment does not appear

Re: HELP!!

Wed, 16 Nov 2016 15:38:32 GMT

this is how i have it in qlikview

Re: HELP!!

Anil_Babu_Samineni — Wed, 16 Nov 2016 15:41:01 GMT

Okay, Then see mayilvahanan Response with Transformation of Month Field in Pivot Table. Check with that

Re: HELP!!

Wed, 16 Nov 2016 15:59:04 GMT

Mayil Vahanan Ramasamy That's the way to show it. I do not see the file

But it is functional with more months ?. For example with the month of April would not be the average between 3 if not between 4. average = total sales / Number of months