Urgent AGGR HELP!!!

[#1]

Hi All,

I am have problems with an AGGR SUM. Hopefully someone has an answer for me.

I have two records:

Supplier Code	Date	Amount
0001	2012/01/01	5000
0001	2013/01/01	8000

What I need to do is roll up these two records into the latest record with a sum of the two amounts like this:

Supplier Code	Date	Amount
0001	2013/01/01	13000

How would I do it? I am using aggr(sum(amount),[supplier code]) the expression aswell as aggr(max(date),[supplier code] as a calculated dimension for the date. The result is two records, with the 13000 populated into the first record.

HELP!!

[swuehl]

Can you do your roll up in the script?

Something like

LOAD

     [Supplier Code],

     Date(max(Date)) as Date,

     sum(Amount) as SummedAmount

resident INPUT group by [Supplier Code];

drop table INPUT;

(or similar in a chart object).

[#3]

I cant do it in the script because the table I want to edit is a copy of an existing table for another client. I would need to do all the calculation within the object. Any ideas?

[swuehl]

[Supplier Code] as dimension, then two expressions:

=Date(max(Date))

=sum(Amount)

[#5]

If I do it that way I still get two records with their seperate amounts. I need the latest record with the total amount of the two records.

[er_mohit]

hi try this

SEE THE ATTACHED FILE ALSO

if(Date(Max(Date,'DD-MM-YYYY'))AND [Supplier Code],sum(Amount))

[swuehl]

Works for me, please check attached (also a calculated dimension using advanced aggregation is working).

I assume your data model or setting differs from what you posted above. Please check and adapt to my sample, then repost.

[#8]

The reason why it isnt working is because I have several other fields that are different on each record. For example, a supplier wasnt contracted but now they are contracted. I tested using only the three fields I mentioned and it works, but as soon as I pull the other fields in (contacted/non contracted), it splits to two records.

EG:

Supplier Code	Date	Contracted	Amount
0001	2012/01/01	NO	5000
0001	2013/01/01	YES	8000

Is there any way to ignore the other fields when aggregating?

[swuehl · Accepted solution]

So you are using Contracted as another dimension, right?

You need to define what you want to show for Contracted, when rolling up your two records into one. Maybe the status for the max date?

=FirstSortedValue(Contracted, -Date)

as expression (only using Supplier Code as dimension).

Besides this, one way to ignore dimensions in aggregations is using the TOTAL qualifier:

=sum(TOTAL<[Supplier Code]> Amount)

Hope this helps,

Stefan

[#10]

Thank you so much for your help Stefan. I used the FirstSortedValue function and kept Supplier Code as the only dimension and it worked perfectly!