topic Re: Conditionally sum or group a value in App Development

Conditionally sum or group a value

JeroenHoltrop — Sat, 16 Nov 2024 18:45:23 GMT

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

Re: Conditionally sum or group a value

tresB — Tue, 21 Apr 2020 14:11:03 GMT

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))

Re: Conditionally sum or group a value

JeroenHoltrop — Tue, 21 Apr 2020 15:59:37 GMT

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!

Re: Conditionally sum or group a value

tresB — Wed, 22 Apr 2020 05:14:08 GMT

Like this?

Re: Conditionally sum or group a value

JeroenHoltrop — Wed, 22 Apr 2020 05:51:09 GMT

Yes, of course, thanks a lot!