# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

New Contributor III

## Average sum group by months in load script

Good day!

In load script i load table from excel:

Main:

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.

1 Solution

Accepted Solutions
Honored Contributor II

## Re: Average sum groub by months in load script

Main:

Avg(Sum) as AvgSum,

YearMonth

Group by

YearMonth;

Id,

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

Sum

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;

11 Replies
Honored Contributor II

## Re: Average sum groub by months in load script

Main:

Avg(Sum) as AvgSum,

YearMonth

Group by

YearMonth;

Id,

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

Sum

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;

MVP

## Re: Average sum groub by months in load script

Data:

Id,

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

Sum

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

Group:

noconcatenate

YearMonth,

avg(Sum) as AvgSum

Resident Data

group by Id,YearMonth;

drop table Data;

New Contributor III

## Re: Average sum group by months in load script

Thanks!!

How i can add filed id Id to result table?

Esteemed Contributor

## Re: Average sum groub by months in load script

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

New Contributor III

## Re: Average sum group by months in load script

But i need to do it in script level.

Honored Contributor II

## Re: Average sum group by months in load script

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?

New Contributor III

## Re: Average sum group by months in load script

Id     YearMonth     Sum

1     201501     8.2

1     201502    11.8

Honored Contributor II

## Re: Average sum group by months in  load script

is id always 1?

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

Honored Contributor II

## Re: Average sum group by months in load script

Main:

min(Id) as Id,

Avg(Sum) as AvgSum,

YearMonth

Group by

YearMonth;

Id,

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

Sum

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1)

;