• 0

# Calculate data for current week

## Question

Hi. I am trying to find what is the solution for this. I want to be able to calculate the amount assigned to my agent and also deduct the total money owed of this specific agent. Also, I wanted to display the data and compute the data for the current week only. Is this possible using select statement?

## Recommended Posts

• 0

Hi @Batchini,

If you want to display the data for the current week only, you can use this sample fiormula as reference:

```(SELECT SUM(Paid Field) From Amount_tbl
WHERE Agent_Number = target.[@field:Agent_Number] AND Date >= dateadd(day, 1-datepart(dw, SysDateTime()), CONVERT(date,SysDateTime()))
AND Date < dateadd(day, 8-datepart(dw, SysDateTime()), CONVERT(date, SysDateTime()))) -
(SELECT SUM(Amount_owed) From Amount_tbl
WHERE Agent_Number = target.[@field:Agent_Number] AND
Date >= dateadd(day, 1-datepart(dw, SysDateTime()), CONVERT(date,SysDateTime()))
AND Date < dateadd(day, 8-datepart(dw, SysDateTime()), CONVERT(date, SysDateTime())))```

The two select statements get the total of the 'Paid Field' and 'Amount_owed' fields for the current week only. I hope this helps.

- Barbie

• 0

##### Share on other sites

• 0

@BarbieHello now I think I need to compute an overall total as well without the current week criteria. Can you help me with that? Should I remove some of fields in the formula? How?

##### Share on other sites

• 0

Yes, you just need to remove the  date condition.

It will look like this:

```(SELECT SUM(Paid_Amount_1) From Agent_Invoices_2021
WHERE Agent_Number = target.[@field:Agent_Number]) -
WHERE Agent_Number = target.[@field:Agent_Number])```

I hope that works on your end.

- Barbie

##### Share on other sites

• 0

Hey there! I found that Caspio has an article that lists the functions available. They also indicate which can be used in Calculated Value, Calculated Field, Aggregations and Formula Field.

LOEY

##### Share on other sites

• 0

Hi - If you would like to show the week number of the year like now (It is currently week 39 in 2023) - this can be done by using calculations.

To use it in Calculated Value, you can use this formula:

`Datepart(week,SysDateTime())`

If you would like to display it in the HTML Block (Details Page), you may use a Calculated Field with this formula:

`Datepart(week,SysDateTime()) - 1`

Hope it helps!

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.