Jump to content
  • 0

Calculate data for current week


Kuroshi

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? 

Link to comment
Share on other sites

6 answers to this question

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

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


I hope that works on your end. 

- Barbie

Link to comment
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!

Link to comment
Share on other sites

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.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...