topic Group by on a Date.. in QlikView

Group by on a Date..

Tue, 04 May 2010 21:15:15 GMT

Hello all!

I have a table with currency rates per day and I would like to calculate an average rate per month. Why doesnt my script below work?

The currency rate table (tmpCurrencyRates) looks like this:

Date, USD,DKK,NOK

2009-02-03 1,232 4,123 2,341

2009-02-04 1,543 4,765 2,567

---------------------------------------

script:

CurrencyRates:

load

date(date#(left(Date, 7), 'YYYY-MM'), 'YYYY-MM') as CurrencyPeriod,

avg(USD) as aUSD,

avg(DKK) as aDKK,

avg(NOK) as aNOK

resident tmpCurrencyRates

group by CurrencyPeriod;

Cristian

Tue, 04 May 2010 21:23:46 GMT

try with

CurrencyRates:

load

date(date#(left(Date, 7), 'YYYY-MM'), 'YYYY-MM') as CurrencyPeriod,

avg(USD) as aUSD,

avg(DKK) as aDKK,

avg(NOK) as aNOK

resident tmpCurrencyRates

group by date(date#(left(Date, 7), 'YYYY-MM'), 'YYYY-MM');

Tue, 04 May 2010 21:31:26 GMT

Worked great! Thank you!

Cristian