Aggr function

[saumyashah90]

Hi

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.

[sunny_talwar]

Try this:

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

[maxgro]

try

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

[saumyashah90]

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

[saumyashah90]

i wan this output together with all other week.

[saumyashah90]

Update?

[sunny_talwar · Accepted solution]

May be this?

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

[saumyashah90]

looks good thanks

[maxgro]