topic Re: Aggr function in QlikView

Aggr function

saumyashah90 — Mon, 14 Nov 2016 10:56:24 GMT

Please find attached my excel and qvw for expression used.

I have weekly data representation for two countries.

For a given week(Oct 3rd to Oct 9th) , for one country data comes till 8th and other country data comes till 9th .

Now i have to show display amount at the end of each week.

When i select country separately i get correct data but when i dont select any or select both together i get data only for other country having data till 9th.

I want sum of amount for maximum dates for that week.

for example attached.

For a given week(Oct 3rd to Oct 9th)

SG is having data till 8th

HK is having data till 9th

When i select country differently i get correct answer .But when i select it together i get amount for HK only.

Re: Aggr function

sunny_talwar — Mon, 14 Nov 2016 12:24:58 GMT

Try this:

Sum(Aggr(FirstSortedValue(Aggr(Sum({<type={'AUM'}>}amount),weekseries,date, country),-Aggr(date,weekseries,date, country)), country))

Re: Aggr function

maxgro — Mon, 14 Nov 2016 12:41:31 GMT

try

sum(aggr((FirstSortedValue(aggr(sum(amount), country,weekseries,date), -date)), country, weekseries))

Re: Aggr function

saumyashah90 — Tue, 15 Nov 2016 02:25:16 GMT

Hi I am getting only Week 3-9 as output...i want this same output with all the weeks

Re: Aggr function

saumyashah90 — Tue, 15 Nov 2016 02:26:40 GMT

i wan this output together with all other week.

Re: Aggr function

saumyashah90 — Tue, 15 Nov 2016 06:19:35 GMT

Update?

Re: Aggr function

sunny_talwar — Tue, 15 Nov 2016 07:10:35 GMT

May be this?

Sum(Aggr(FirstSortedValue(Aggr(Sum({<type={'AUM'}>}amount),weekseries,date, country),-Aggr(date,weekseries,date, country)), country, weekseries))

Re: Aggr function

saumyashah90 — Tue, 15 Nov 2016 08:18:49 GMT

looks good thanks

Re: Aggr function

maxgro — Tue, 15 Nov 2016 08:53:38 GMT