Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All.
I’m hoping you can assist with this basic formula:
I am trying to total up transactions (AMOUNT) up and until (but not including) the month selected.
So, if I select 2013 (YEAR), May (MONTH), I would like the column to show the total of AMOUNT from the beginning of time until the end of April 2013.
Any ideas?
Thanks again
Your PERIODKEY field should preferably be a number. YEAR*12+num(MONTH) or YEAR*100+Month would be better. Then this should work:
sum({<YEAR=,MONTH=,PERIODKEY={'<$(=max(PERIODKEY))'}>}AMOUNT)
But if PERIODKEY is a string than maxstring ought to work too, maybe just a bit slower.
Let's suppose you have a date filed like "MMM-YYYY" (ex: Jan-2013)
Now your formula should look like:
SUM({$<Date = {"<$(=getcurrentselections(Date))"}>} Amount)
You're going to need a year-month field if you don't have a date field already. If you have a date field you can use this:
sum({<YEAR=,MONTH=,MyDate={'<$(=monthstart(max(MyDate)))'}>}AMOUNT)
Thanks for the prompt reply Gysbert
I'm working with pieta1230 on this, and have tried your formula above.
I have a PERIODKEY field which I bring through as YEAR & num(MONTH,'00') as PERIODKEY.
When i use your formula, i replace 'MyDate' with 'PERIODKEY' and the result of the selection:
YEAR=2013
MONTH=FEB
brings through all data until the end of 2012, but does not include transactions from Jan 2013..
Any ideas?
Your PERIODKEY field should preferably be a number. YEAR*12+num(MONTH) or YEAR*100+Month would be better. Then this should work:
sum({<YEAR=,MONTH=,PERIODKEY={'<$(=max(PERIODKEY))'}>}AMOUNT)
But if PERIODKEY is a string than maxstring ought to work too, maybe just a bit slower.
Thanks Gysbert - that did the trick!!