0

Display quarterly results

I want to display the quarterly results from my invoices. I allready have the formula "quarter(Factuurdatum)" but I don't see how I can get the results. For now my formula is as follows: sum((select Kosten).Bedrag)

 

But I don't know how I have to complete the formula

10replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Something like:

    let q := yearquarter(Today());

    sum((select Kosten where yearquarter(Factuurdatum) = q).Bedrag)

    Steven

    ps: using yearquarter() instead of quarter() gives you only the results of the current year and not the past years.

    Like
    • Fred
    • Fred
    • 11 mths ago
    • Reported - view

    To build on Steven's comments, if you have a formula field called quarter that has:

     

    quarter(Factuurdatum)

     

    Then you can find all quarter 1 records regardless of year by using:

     

    sum((select Kosten[quarter = 1]).Bedrag)

     

    the square brackets are another way of doing a where statement.

    Like
  • Thanks for the feedback, guys - both work but when I use the following formula, I get the results of Q4 2021:

     

    let q := yearquarter(Today());

    sum((select Kosten where yearquarter(Factuurdatum) = q).Bedrag)

     

    How can I change the formula so that I can see the results for Q1/2021, Q2/2021, Q3/2021, Q4/2021 and so one? I presume a new formula for each quarter and each year?

    Like
    • Fred
    • Fred
    • 11 mths ago
    • Reported - view

    The formula is working exactly like Steven said it would, see his ps comment.

     

    If you want to create a formula that finds the current year quarter 1 then you can change either:

     

    let q := 1; <- change the value of the variable used in the select statement

    sum((select Kosten where yearquarter(Factuurdatum) = q).Bedrag)

     

    or

    (remove the variable assignment)

    sum((select Kosten where yearquarter(Factuurdatum) = 1).Bedrag) <- hard code the quarter number into the select statement

    Like
  • Thx Fred but unfortunately the result is still 0,00 € (whereas there should be a number). Could it be because the value in yearquarter is displayed as "2021 Q1" instead of "1"?

    Like
    • Fred
    • Fred
    • 11 mths ago
    • Reported - view

    Yes, sorry. I was thinking quarter not yearquarter. Give it a try and let us know.

    Like
  • Jarno,

    I would create a choice field (in this example called Kwartaal) with 4 choices in order Kwartaal1,Kwartaal2,Kwartaal3 en Kwartaal4. With this, you can choose which quarter you want to show. The formula would then be:

    let q := Kwartaal;

    sum((select Kosten where year(Factuurdatum) = year(today()) and quarter(Factuurdatum) = q).Bedrag)

    Steven

    Like
  • Thx guys, but it still does not work I'm afraid. Could ik be because my summary table is a different table than the expenses table? The hierarchy is as follows:

     

    1. Werven (=Construction sites)

    1.a) Kosten (=Expenses)

    1.b) Vorderstaten (=Progress states)

    2. Samenvatting (=Summary)

     

    I tried to get the summary on the "Samenvatting" table where I want to create a dashboard of some sort.

    Like
  • Update - thanks to your input, the puzzle was solved:

     

    sum((select Kosten where year(Factuurdatum) = year(today()) and quarter(Factuurdatum) = q).Bedrag)

     

    ==> instead of "q" I put "1" for the first quarter, "2" for the second quarter etc.

     

    Thanks again!

    Like
  • Jarno,

    Verdere Hulp nodig? Contacteer mij gerust. Mijn gegevens zijn te vinden in de Partner Pagina.

    Steven

    Like
Like Follow
  • 11 mths agoLast active
  • 10Replies
  • 303Views