topic cumulative sum with multiple conditions in App Development

cumulative sum with multiple conditions

bvandenbossche — Sun, 03 Nov 2019 18:17:01 GMT

Hi,

I have a table with a list of invoices, with the invoice date, payment status, amount and if payment status = true also a payment date. Below is a small sample of the dataset:

Now I would like a table with one row per month, which gives for each month the cumulative amount invoiced and the unpaid balance. For the unpaid balance I want to take into account the invoices that are still unpaid today (payment status = false) as well as the invoices that were not yet paid at the end of the period in the current row (so payment date > last day of the month for this row).

The cumulative amount works fine with: RangeSum(Above(Sum([Amount incl VAT]), 0, RowNo())). I'm struggling with the unpaid amount however, this doesn't seem to work:

RangeSum(Above(Sum(

{$<[Payment Status]={'False'}>+

$<[Payment Date.Calendar.Date]={">$( monthend(max([Invoice Date.Calendar.Date])) )"}>}

[Amount incl VAT]), 0, RowNo()

))

Any ideas on how to get this to work are highly appreciated! 🙂

Best regards,

Bart

Re: cumulative sum with multiple conditions

Anil_Babu_Samineni — Mon, 04 Nov 2019 07:05:37 GMT

With in the object, Can you add [Payment Date.Calendar.Date] and [Payment Status] as dimension and see?

Re: cumulative sum with multiple conditions

bartvandenbossche — Mon, 04 Nov 2019 12:41:44 GMT

Hi, I'm not sure I understand your suggestion. If I add those 2 dimensions, I don't get a total per month anymore? Also, it has no effect on the outcome of my expression:

Re: cumulative sum with multiple conditions

sunny_talwar — Mon, 04 Nov 2019 13:30:59 GMT

May be you need this

RangeSum(Above( Sum({$<[Payment Status] = {'False'}, [Payment Date.Calendar.Date] = {">$(=MonthEnd(Max([Invoice Date.Calendar.Date])))"}>} [Amount incl VAT]) , 0, RowNo()))

Re: cumulative sum with multiple conditions

bartvandenbossche — Mon, 04 Nov 2019 13:39:23 GMT

Hi, thanks for the suggestion, but that only returns 0's apparently...

Re: cumulative sum with multiple conditions

sunny_talwar — Mon, 04 Nov 2019 14:05:05 GMT

Would you be able to share a sample to check this out?

Re: cumulative sum with multiple conditions

bartvandenbossche — Mon, 04 Nov 2019 14:19:58 GMT

Sure, here is some sample data for 3 months.