Conditionally sum or group a value

[JeroenHoltrop]

Hi,

I would like to be able to conditionally sum a dimension based on a grouping possibility. The situation is like this:

ID	InvoiceID	GroupInvoiceID	Value
1	12345678	0	50
2	23456789	0	150
3	0	99999999	450
4	0	99999999	600
5	0	99999999	230

We make a split between actual invoices and grouped invoices. What happens in our back-end system, is that we first calculate and store an invoice and then group them together. This results in a "0" value in the InvoiceID and a grouped invoiceID, which binds the invoices together. 

Now, with this, I would like to be able to show the values of a single invoice, so my report should result in 3 records: ID 1, 2 and a combination of 3-5 with a combined value of 1280. 

I cannot find a way to conditionally sum the value amounts based on the fact that the GroupInvoiceID is the same.

Can somebody help me with this?

Thanks a lot  

[tresesco]

I would prefer a script solution for this. However, a UI solution could be using calculated dimension like:

=If(GroupInvoiceID=0, ID, Aggr(NODISTINCT Min(ID)&'-'&Max(ID),GroupInvoiceID))

[JeroenHoltrop]

Thanks for your reply. I see now that I left 1 important bit out, which basically is my need to still represent invoices 3-5, not grouped, but as single sub-invoices, but the total field should be summed. 

InvoiceID	invoiceNumber	Value
1	12345678	50
2	23456789	150
3	99999999	1280
4	99999999	1280
5	99999999	1280

Reason for this, is that I need to show the details of all the subinvoices, with the total as full invoice amount.

Do you have an idea how to do that?

Thanks!

[tresesco · Accepted solution]

Like this?

[JeroenHoltrop]

Yes, of course, thanks a lot!