0

Formula for YTD Calculations

I am trying to create a formula that sums the sales from the year.

 

So far I have (but is not working)

 

let d :=today();

sum(Invoice.'Invoice Sum' where year(Invoice.'Date Completed'=year(d)

 

Please help!

 

Additionally, I am aslo trying to create a sum that calculates something that resets on the first of January every year.

For example, sum(time exceptions) equals 10 which is added up from events throughout the year, where (for example) today is December 31.. I would like the sum(time exceptions) to start calculating over after the new year, where after December 31, the sum=0 until events are added for that time. 

9 replies

null
    • Nick
    • 5 yrs ago
    • Reported - view

    let d :=today();

    sum(Invoice where year(Invoice.'Date Completed'=year(d).'Invoice Sum'))

    • Nick
    • 5 yrs ago
    • Reported - view

    Sorry...

    let d :=today();

    sum(Invoice where year(Invoice.'Date Completed'=year(d)).'Invoice Sum')

    • SMoore
    • 5 yrs ago
    • Reported - view

    that formula does not work. 

    • Nick
    • 5 yrs ago
    • Reported - view

    let y := year(today());

    sum(Invoice where year('Date Completed' = y).'Invoice Sum')

    • SMoore
    • 5 yrs ago
    • Reported - view

    I am also receiving an error after "where"

    • Ninox partner
    • RoSoft_Steven.1
    • 5 yrs ago
    • Reported - view

    Shouldn't there be a 'select' statement before Invoice?

    sum(select Invoice where year('Date Completed' = y).'Invoice Sum')

    Not sure but it's worth a try.

    Steven.

    • SMoore
    • 5 yrs ago
    • Reported - view

    Still No luck. 

    • SMoore
    • 4 yrs ago
    • Reported - view

    .

    • Database Consultant
    • Kirkness_Associates_Ju
    • 4 yrs ago
    • Reported - view

    This should work for the YTD value:

    sum((select Invoice)[year('Date Completed') = year(today())].'Invoice Sum')

    I'm not quite sure what you mean with your second example - although it sounds very similar?

Content aside

  • 4 yrs agoLast active
  • 9Replies
  • 1261Views