I have a question, that might be simple to answer, but I can't figure out the solution.
I have a field called 'dollars' in it, there is data about both actual expenditure and forecast expenditure. The way you distinguish between the two is if yearmonth<today then its actual and if yearmonth>today is forecast. I have a field that is calculating the spendtodate as an aggregation of what is happening in th dollars field. so for example, if I have jan-10, feb-10, march-10, april-10, may-10 then my values for the spendtodate are jan-0, feb-10, march-20, april-30, may-40.
When I do a bar graph its working well because I have yearmonth as my x axis. However the issue is when I try to have it in terms of a row. When I pull in the spendtodate field, its showing me the sum of all the values in it.
How do I create a calculated field that shows me the amount spend to date?
In sql it would look something like:
You can replicate this behavior by simply not returning the result if the "where condition" is not matched.
IF [Year] = 2015 THEN [Sales] END (Notice 2014 and 2013 results come back as null)