topic Aggregate Function Help! in QlikView

Aggregate Function Help!

Anonymous — Fri, 29 Nov 2013 02:35:57 GMT

Hi,

I am having a problem with aggregate function which I thought I knew how it works but now I am just confused.

Scenario:

I have a set of data that looks like the image below, what I am trying to do now is to have a chart that shows the sum(Amount) of the last month of each year, therefore, the desired result should should be:

2012 : 30

2013 : 50

I have tried the expression below which I thought aggregate function should return me with the latest month of each year but it didn't give me the desired result but just empty cells :

sum({<Date={"$(=aggr(max(Date),Year))"}>} Amount)

I have attached the test application that contain the data for this scenario, appreciate if anyone can help. Thank you.

Regards.

Re: Aggregate Function Help!

tresB — Fri, 29 Nov 2013 05:26:19 GMT

Use:

=FirstSortedValue( AGGR(Sum(Amount),Date), -AGGR(Date,Date))

PFA

Re: Aggregate Function Help!

Anonymous — Fri, 29 Nov 2013 06:19:21 GMT

Hi,

see below scipt

hi see the below script,

tt:

LOAD year,

yearmonth,

amount

FROM

xyz.xls;

Left join

load

year,

max(yearmonth) as maxdates

resident tt group by year;

load

year,

if(yearmonth= maxdates,'y','n') as flag,

amount

resident tt;

drop Table tt;

now use =sum({<flag={'y'}>}amount) in your chart

anant

Re: Re: Aggregate Function Help!

sudeep_d — Fri, 29 Nov 2013 06:26:49 GMT

Hi ,

Create a variable

maxmnth=month(max(Date))

then the expression wud be

sum(if(month(Date)=maxmnth,Amount))..

See the attached file.

Cheers!!!

Re: Aggregate Function Help!

Anonymous — Fri, 29 Nov 2013 06:31:50 GMT

Thank you Tresesco, it solved my problem!