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 • Ninox partner
• RoSoft_Steven.1
• 1 yr ago
• Reported - view

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.

• Fred
• 1 yr 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.

• Jarno_Vande_Walle
• 1 yr ago
• Reported - view

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?

• Fred
• 1 yr 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

• Jarno_Vande_Walle
• 1 yr ago
• Reported - view

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

• Fred
• 1 yr ago
• Reported - view

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

• Ninox partner
• RoSoft_Steven.1
• 1 yr ago
• Reported - view

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

• Jarno_Vande_Walle
• 1 yr ago
• Reported - view

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.

• Jarno_Vande_Walle
• 1 yr ago
• Reported - view

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!

• Ninox partner
• RoSoft_Steven.1
• 1 yr ago
• Reported - view

Jarno,

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

Steven

## Content aside

• 1 yr agoLast active
• 10Replies
• 317Views