Calculate data for current week

[Kuroshi]

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? 

[Barbie]

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

[Barbie]

Here's an article which you can refer to for more information about SQL functions: https://howto.caspio.com/function-reference/

[Kuroshi]

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

[Barbie]

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]) -
(SELECT SUM(Amount_Additional_1) From Agent_Invoices_2021
WHERE Agent_Number = target.[@field:Agent_Number])

I hope that works on your end. 

- Barbie

[ParkLoey]

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.

https://howto.caspio.com/function-reference/

- LOEY

[Kurumi]

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!