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
10 replies

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.

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.

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?

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

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"?

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

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

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.

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!

Jarno,
Verdere Hulp nodig? Contacteer mij gerust. Mijn gegevens zijn te vinden in de Partner Pagina.
Steven
Content aside
 2 yrs agoLast active
 10Replies
 332Views