topic Re: Agg for multiple data set in QlikView

Avg for multiple data set

Mon, 14 Jul 2014 09:57:03 GMT

Hello,

could you please help me to calculate average for multiple columns in a row.

TTR-AVG-ALL will be the average of TTR-P1 , TTR-P2,..TTR-P5 for a month. how can I get this in chart in qlikview.

Month	TTR-P1	TTR-P2	TTR-P3	TTR-P4	TTR-P5	TTR-AVG-ALL
Nov 2013	100.00	100.00	100.00	100.00	-
Dec 2013	100.00	100.00	100.00	100.00	-
Jan 2014	100.00	100.00	98.94	100.00	-
Feb 2014	-	100.00	100.00	100.00	-
Mar 2014	-	100.00	100.00	99.92	-
Apr 2014	-	100.00	100.00	100.00	-
May 2014	-	100.00	100.00	100.00	-

Re: Agg for multiple data set

amit_saini — Mon, 14 Jul 2014 10:04:19 GMT

Maniprakash,

Better you can use here Cross Table concept , this will easily help you to get desired average.

Please see the attachment.

Thanks,
AS

Re: Agg for multiple data set

Mon, 14 Jul 2014 10:07:59 GMT

Hi Amit, I could not able to open the attachment. Getting error " Failed to open document"

Re: Agg for multiple data set

amit_saini — Mon, 14 Jul 2014 10:12:28 GMT

Are you using personnel addition or Licensed one????

Please see below to understand Cross table concept:

Cross Table Concept:

A cross table is a common type of table featuring a matrix of values between two orthogonal lists of header data. It could look like the table below:

Example:

a.csv

Year	Jan	Feb	Mar	Apr	May
1991	45	65	78	12	78
1992	11	23	22	22	45
1993	65	56	22	79	12
1994	65	24	32	78	55
1995	45	56	35	78	68

If this table is simply loaded into QlikView, the result will be one field for Year and one field for each of the months. This is generally not what you would like to have. One would probably prefer to have three fields generated, one for each header category (Year and Month) and one for the data values inside the matrix.

This can be achieved by adding the crosstable prefix to your load or select statement.The statement for loading this cross table could be:

crosstable (Month, Sales) load * from a.csv;

The result in QlikView would be as follows:

Year	Month	Sales
1991	Jan	45
1992	Feb	11
1993	Mar	65
1994	Apr	65
1995	May	65
		23
		56
		24

Thanks,