Hi all,

I'm working on a project to graph the spendings, recorded daily. I'd like to summarize the spendings to months and weeks view. Months can be easily achieved, however, outsystems does not have a Week() function to obtain the number of the current week. I've tried the extension Extended Date Time as well, however, the WeekNumber() function cannot be used in an aggregate. 

My thinking is to manually cycle through the aggregate, then binding the data to a list while summing the spendings for those who's week number are the same. This, however, seems rather unnecessary and more brute-force style.

Would appreciate any recommendations.

Thank you,



Hi Daniel,

If a week number suffices, you could write an Advanced Query and use DATEPART to get a week number, and then group your data by this value.

Here's an example:

Will return:

Hi Daniel,


Is SQL Server ? Can you change the aggregate to Sql query ?

If yes, see this function DATEPART https://www.w3schools.com/sql/func_sqlserver_datepart.asp


SELECT DATEPART(wk, '2017/08/25') AS DatePartInt;


Result:


Regards

Afonso Carvalho wrote:

Hi Daniel,

If a week number suffices, you could write an Advanced Query and use DATEPART to get a week number, and then group your data by this value.

Here's an example:

Will return:


Hi,


Thank you both for your response. I am not too familiar with SQL so I'm attempting to ride the query now, but I cannot convert the aggregate to SQL as it lacks the button to convert to SQL in both the preparation and the action screen.

Hello,

You can get this extension from the forge, there you can use the week number option. I think is the best and easiest way to get what you need.


https://www.outsystems.com/forge/Component_Overview.aspx?ProjectId=90

Hope this helps you.

Hugo Jorge wrote:

Hello,

You can get this extension from the forge, there you can use the week number option. I think is the best and easiest way to get what you need.


https://www.outsystems.com/forge/Component_Overview.aspx?ProjectId=90

Hope this helps you.

Hi Hugo, 

thanks for your response, but i've used this extension and found that it's not applicable in aggregates. 

In the end, I did a roundabout way of doing it by comparing the given date to the beginning of the year, then calculating how many weeks have passed... not very efficient but it gets the job done.