1 Reply Latest reply on Mar 22, 2016 9:52 AM by Derrick Austin

    Equivalent of 'where' clause in Calc Field

    Haroldy Tevaras

      Hi!

       

      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:

       

      select [spendtodate]

      from table

      where yearmonth<getdate()

       

      Thanks!