topic Re: Average sum groub by months in load script in QlikView

Average sum group by months in load script

Anonymous — Thu, 11 Jun 2015 13:39:39 GMT

Good day!

In load script i load table from excel:

Main:

LOAD

Id,

Year(Date)&num(Month(Date),'00') as YearMonth,

avg(Sum)

FROM

$(DataPath)Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;

in result i get table:

Id	Date	Sum
1	01.01.2015	4
1	02.01.2015	6
1	03.01.2015	4
1	04.01.2015	6
1	05.01.2015	37
1	06.01.2015	4
1	07.01.2015	2
1	08.01.2015	7
1	09.01.2015	4
1	01.02.2015	4
1	02.02.2015	6
1	03.02.2015	3
1	04.02.2015	6
1	05.02.2015	3
1	06.02.2015	1
1	07.02.2015	3
1	08.02.2015	5
1	09.02.2015	76

How i load script calculate average sum group by YearMonth?

My necessary result:

Id YearMonth Sum

1 201501 8.2

1 201502 11.8

Thanks.

Re: Average sum groub by months in load script

giakoum — Thu, 11 Jun 2015 13:48:02 GMT

Main:

LOAD

Avg(Sum) as AvgSum,

YearMonth

Group by

YearMonth;

LOAD

Id,

Year(Date)&num(Month(Date),'00') as YearMonth,

Sum

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;

Re: Average sum groub by months in load script

Kushal_Chawda — Thu, 11 Jun 2015 13:52:17 GMT

Data:

Id,

Year(Date)&num(Month(Date),'00') as YearMonth,

Sum

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

Group:

noconcatenate

load Id,

YearMonth,

avg(Sum) as AvgSum

Resident Data

group by Id,YearMonth;

drop table Data;

Re: Average sum group by months in load script

Anonymous — Thu, 11 Jun 2015 13:53:16 GMT

Thanks!!

How i can add filed id Id to result table?

Re: Average sum groub by months in load script

awhitfield — Thu, 11 Jun 2015 13:53:30 GMT

Hi,

Just set the Dimension to YearMonth and the expression to Avg(Sum) and format the number as Integer 1dp

And you'll get:

Just add ID as another dim if required.

HTH Andy

Re: Average sum group by months in load script

Anonymous — Thu, 11 Jun 2015 13:56:06 GMT

Thanks for advice.

But i need to do it in script level.

Re: Average sum group by months in load script

giakoum — Thu, 11 Jun 2015 13:59:02 GMT

depends

if you have multiple ids per year-month (and I guess you do) then you can't

How do you want it to look?

Re: Average sum group by months in load script

Anonymous — Thu, 11 Jun 2015 14:00:43 GMT

Id YearMonth Sum

1 201501 8.2

1 201502 11.8

Re: Average sum group by months in load script

giakoum — Thu, 11 Jun 2015 14:04:28 GMT

is id always 1?

if yes, just add min(id) as id in the first load

Re: Average sum group by months in load script

giakoum — Thu, 11 Jun 2015 14:05:07 GMT

Main:

LOAD

min(Id) as Id,

Avg(Sum) as AvgSum,

YearMonth

Group by

YearMonth;

LOAD

Id,

Year(Date)&num(Month(Date),'00') as YearMonth,

Sum

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;

Re: Average sum group by months in load script

Anonymous — Thu, 11 Jun 2015 14:53:23 GMT

no. not only 1.

There will be more values

Re: Average sum group by months in load script

pratap6699 — Thu, 11 Jun 2015 16:37:21 GMT

=aggr(avg(sales),month)