Chart problem: Sum of everything below a date with set analysis

[#1]

Hello all,

I'm having a problem with a chart. It's a line chart which needs to display a sum of all "amounts" which have a posting date lower then the date on the X-axis and where the due date at that moment was not passed. Due date is a field that comes from the table CUSTOMERLEDGERENTRY, Amount is a field that comes from the DETCUSTOMERLEDGERENTRY table.

I have one dimension: "Select Posting MonthYear" which comes from a calendar that is not linked with the rest of my datamodel, this calendar also has a field Select Posting Date.

I'm using following expression:

sum({<[DETCUSTOMERLEDGERENTRY Posting Date]={"<=$(=max([Select Posting Date]))"}>} If([CUSTOMERLEDGERENTRY Due Date]>vSelectedDate,[DETCUSTOMERLEDGERENTRY Amount]))

It now shows the total sum as of today in a straight line over all the periods. But it should gradually change as for every month this could (and should) be different.

What am I doing wrong?

Don't hesitate to ask for more info...

[Oleg_Troyansky]

you probably expect the Set Analysis formula, such as max(...date) to be calculated for each month, but unfortunately it's impossible. Set Analysis condition only recalculates once per chart. Therefore, the only working solution is to expand your IF condition (as much as I despise using IF(), it's your only choice in this situation.

If you have a large data set, you might have to do some more advanced data modelling to solve this problem through proper associations between the transactional date(s) and the calendar...