topic Re: help with aggregation in QlikView

help with aggregation

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

My table chart shows wrong values ( Month, Feb) 149 when it is expanded.

when i choose only Feb the expression is correct. 139

variable:

=Day(MonthEnd(Max(Dato)))

Expression:

sum({<PRISTABID={'745'}>} distinct ELEMENTPRIS/366*vDaysMonth)

Re: help with aggregation

migueldelval — Tue, 27 Dec 2016 08:58:36 GMT

Hi Marius,

Try this expression:

=(sum({<PRISTABID={'745'}>} ELEMENTPRIS)/366)*vDaysMonth

Regards

Miguel del Valle

Re: help with aggregation

Anonymous — Tue, 27 Dec 2016 09:06:34 GMT

Not correct..

now i got this output:

the one to the left is correct, but the feb month shows 149 when it should show 139.

when i select only february it shows 139

Re: help with aggregation

migueldelval — Tue, 27 Dec 2016 09:10:51 GMT

Hi Marius,

What kind of master calendar are you using?

Regards

Miguel del Valle

Re: help with aggregation

Anonymous — Tue, 27 Dec 2016 09:15:53 GMT

Not sure, i have used the same for several years.. script file.

i thing the problem is my variable or something..

Re: help with aggregation

migueldelval — Tue, 27 Dec 2016 09:20:50 GMT

Hi Marius,

I think that it´s good always use a master calendar to handdle days/month/year.

You can make a new field in your script with days of each month.

If you attach an example I would help you.

Regards

Miguel del Valle

Re: help with aggregation

Anonymous — Tue, 27 Dec 2016 09:25:24 GMT

se attached.

my variable dosent aggregate between months so it uses only 31 for all months when the table shows jan-des.

Re: help with aggregation

Anonymous — Tue, 27 Dec 2016 09:34:00 GMT

solved it;

aggr(Day(MonthEnd(Max(Dato))),Måned,Kontraktsgruppenavn, Målepunktid))

Re: help with aggregation

migueldelval — Tue, 27 Dec 2016 09:40:46 GMT

Perfect Marius,

But red this article, I think that could be interesting for you.

Calendars

Regards

Miguel del Valle

Re: help with aggregation

Anonymous — Tue, 27 Dec 2016 09:50:15 GMT

Thanks

One question;

i have "ELEMENTPRIS, Effektledd" for each month

01.01-01.02

01.02-01.03

01.03-01.04 and so on.

but i only have "ELEMENTPRIS, Energiledd" with a range of several months.

01.01-01.04

01.04-01.09

how can i create Elementpris, Energiledd for each month ?

Re: help with aggregation

migueldelval — Tue, 27 Dec 2016 09:54:43 GMT

Hi Marius,

What it´s the meaning of ELEMENTPRIS, Effektledd and ELEMENTPRIS, Energiledd?

Regards

Miguel del Valle

Re: help with aggregation

Anonymous — Tue, 27 Dec 2016 10:01:08 GMT

Elementpris is the price table/field.

you can see it in the attached example.

Re: help with aggregation

sankartalluru — Tue, 27 Dec 2016 10:29:20 GMT

Hi,

Max command doesn't work in Variable.

Please write the expression in script where Date and ELEMENTPRIS columns are coming.

ELEMENTPRIS/366*day(monthend(Date)) as FInalELEMENTPRIS

and now we can use the above column in charts.

Re: help with aggregation

migueldelval — Tue, 27 Dec 2016 12:07:52 GMT

Hi Marius,

If you need to show information for a range based in natural months you can use month(Dato) in your script and use this field as dimension in your table.

If your range are specially you only need to make a condion in your script and use it as dimension in your table. Eg:

IF(

month(Dato)='Jan' or month(Dato)='Feb',

'Ene-Feb',

If(

month(Dato)='Mar' or month(Dato)='Apr',

'Mar-Apr',

If( ...

as Range_Month,

But I repeat, always it´s better use a Master Calendar to manage a Date.

Regards

Miguel del Valle

Re: help with aggregation

sunny_talwar — Tue, 27 Dec 2016 12:42:36 GMT

I don't see a reason to use Aggr() function here.... why not just this:

Day(MonthEnd(Max(Dato)))

Re: help with aggregation

migueldelval — Tue, 14 Mar 2017 09:52:03 GMT

Hi Marius,

Did you solve your Question?

Regards

Miguel del Valle