How to get the sum of the last day of the month before current selection?

[#1]

I've got a couple of ledger entries and I want to get the sum of the last day of the month before current selection. Let's assume I've got the following values:

AccountNo	Date	Value
4711	01/10/11	500
4711	15/10/11	-400
4711	31/10/11	-300
4711	01/11/11	300
4711	16/11/11	-750
4711	30/11/11	-250
4711	30/11/11	150
4711	30/11/11	-400
4711	01/12/11	-300
4711	31/12/11	-150

Now, when I select Jan 2012, I want to get -150. When I select Nov 2011, I want to get -300. And when I select Dec 2011, I want to get -250 + 150 + -400 = -500.

I played around with Sum() and its set_expression. And I tried FirstSortedValue with TOTAL <AccountNo,Date>. But nothing gave me the right value. How would you do?

[nstefaniuk]

hi

You can do it with set analysis and a variable.

Create a chart with this expression : sum({$<Date={"$(vPreviousDate)"}>}Value)

and a variable with this expression : =max({$<Date={"<$(=Date)"}>}Date)

and it works.

[#3]

That doesn't work. I guess because when I select just Month and Year then Date isn't unique. So, it will return Null.

[nstefaniuk · Accepted solution]

Ok so use this code for the variable:

=max({1<Date={"<$(=min(Date))"}>}Date)

and this for the expression:

sum({$<Date={"$(vPreviousDate)"}, Period=>}Value)

assuming that your field for the month name is named "Period"

Remember that if you want to select something in a listbox (a period for example) and display other thing in a chart, you can't use $ in set analysis, or you have to disable the dimension like that : Period=

You can see in the attached file how it works.

[#5]

Works! I didn't use exactly your code, but I used the idea behind. I add a field to my Calendar giving me the last day of the last month. And this field as well as wildcard on Month and Year I used in set_expression of Sum().

Thanks a lot.