I have an expression that gets my prior month sum of my income accounts (this is only a portion of the expression). I have it working correctly except for the Jan month as I want it to get Dec of the previous year. I have tried several things but can't seem to get it to work and any help would be greatly appreciated. Thanks!
Sorry Rwb139, it seems you have a big trouble in your data cloud... 1) First of all you should have dates to refer to rather than strings ('Jan', 'Feb' ...). This is due to serious performance problems, and because not being numerical, they cause you just what you say.
2) I didn't understand if this piece of string, you are using it in the ETL or in an expression. If it were an expression I would advise you to review everything because if the data were to increase you would have very serious performance problems. 3) If possible, I would ask you to explain exactly what result you want to achieve, because as it is written, it is very difficult to understand (but it is definitely my problem alone !!)
I would like one column for the beginning balance and another column for the ending balance. Please let me know if I can explain more. Thanks for your help!
Ok. Then the first thing to do is unpivot (use the CrossTable() function in ETL) the table in order to have a real table with real dates to compare. In this way you also have many advantages in the calculation functions of your objects. With unpivot you should be able to get a table that looks something like this: field1, field2... property_id, gl_account_number, Date, Value
in a second step, you can make another table, joined, with: Year, beginning_balance
You can join this second table with the first, using a date, or using the year of the date in another field....
Now you can write an expression sum with this date and the previous month... easly with some set analysis or everything you want...
