Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

bibopipo
Not applicable

set analisys troubles

Hi,

I don't know why the following expression doesn't work properly:

=aggr(sum({<MYMONTH={ "<= $(=only(PURCHASE_MONTH))"}, MY_YEAR ={"=$(CURRENTYEAR)-1)"}>} Purchase_amount), Supplier_code)

What I would have is the sum of purchase amount till the selected month (MY_MONTH), but for the previuos year (2011).

It doesn't recognize the ..{"= $(currenyear)-1)....  expression. The result is nothing.

The following expression works properly

=aggr(sum({<MYMONTH={ "<= $(=only(PURCHASE_MONTH))"}, MY_YEAR ={"= $(CURRENTYEAR)"}>} Purchase_amount),Supplier_code)

I really don't understand. Help please

Thanks in advance

1 Solution

Accepted Solutions
celambarasan
Not applicable

Re: set analisys troubles

You have DateField?If so better go with that.

Example:

=aggr(sum({<DateField={">=$(=YearStart(AddYears(Max(DateField),-1)))<=$(=AddYears(Max(DateField),-1
))"},MYMONTH=,MY_YEAR>} Purchase_amount), Supplier_code)

or based on your Expression it will be

=aggr(sum({<MYMONTH={ "<= $(=only(PURCHASE_MONTH))"}, MY_YEAR ={$(=$(CURRENTYEAR)-1))}>} Purchase_amount), Supplier_code)

5 Replies
v_iyyappan
Not applicable

Re: set analisys troubles

Hi

Try like this

=aggr(sum({<MYMONTH={ "<= $(=only(PURCHASE_MONTH))"}, MY_YEAR ={"=$(AddYears(CURRENTYEAR - 1)"}>} Purchase_amount), Supplier_code)

Regards

Iyyappan

celambarasan
Not applicable

Re: set analisys troubles

You have DateField?If so better go with that.

Example:

=aggr(sum({<DateField={">=$(=YearStart(AddYears(Max(DateField),-1)))<=$(=AddYears(Max(DateField),-1
))"},MYMONTH=,MY_YEAR>} Purchase_amount), Supplier_code)

or based on your Expression it will be

=aggr(sum({<MYMONTH={ "<= $(=only(PURCHASE_MONTH))"}, MY_YEAR ={$(=$(CURRENTYEAR)-1))}>} Purchase_amount), Supplier_code)

jontydkpi
Not applicable

Re: set analisys troubles

Hi

I think this shoud work:

=Aggr(sum({<MYMONTH={"<=$(=Only(PURCHASE_MONTH))"}, MY_YEAR ={"=$(=CURRENTYEAR-1)"}>} Purchase_amount), Supplier_code)

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
bibopipo
Not applicable

Re: set analisys troubles

Hi Iyyappan V,

it doesn't work. I get the message Error dimensions

Thanks anyway

bibopipo
Not applicable

Re: set analisys troubles

Thanks very much  Celambarasan

and thanks to anyone has spent his time to help me.

stefan