Solved: Data displayed by week

Kepcla

Hi Guys!
There is some way to display a gallery shows the data per week, that is, a record with the data for the week from Thursday to the following Thursday, and so on, that is, each week from Thursday to Thursday.

happyume

If the above meets the requirements, then I have done this as below:
1 - My sample data is in collection called colSample. But this can be your datasource as well. My column names are RecordDate and Value

2 - I create a collection which adds week number to each date

3 - O then groupby on this weeknumber

ClearCollect(
    col_weeknum_Added,
    AddColumns(
        colSample,
        'WeekNum',
        WeekNum(RecordDate)
    )
);
ClearCollect(
    colGrouped,
    GroupBy(
        col_weeknum_Added,
        WeekNum,
        Value
    )
);

4 - I bind the gallery to colGrouped and label controls has following values:

Date From:

First(
    SortByColumns(
        Filter(
            col_weeknum_Added,
            WeekNum = ThisItem.WeekNum
        ),
        "RecordDate",
        SortOrder.Ascending
    )
).RecordDate

Date To:

First(
    SortByColumns(
        Filter(
            col_weeknum_Added,
            WeekNum = ThisItem.WeekNum
        ),
        "RecordDate",
        SortOrder.Descending
    )
).RecordDate

Total:

Sum(ThisItem.Value,Value)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If this post or my previous reply was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

poweractivate

Check if you can adapt the solution I recently gave here: Get the second sunday date of march for the current year
except to perhaps change it to get the second Thursday instead of the second Sunday.

//change gloFirstDayOfWeek = 0 to:
gloFirstDayOfWeek = 5

and to change it to get the first Thursday rather than second Thursday:

//change these to get the occurrence in the first week instead of second week:
        gloFirstDayOfWeek = 5,
        gloFirstDayOfMonth,
        DateAdd(gloFirstDayOfMonth, 7 - gloFirstDayOfWeek, TimeUnit.Days)

and change this to get it for April instead of March:

// Set the year and month for which you want the second Sunday
Set(gloYear, 2024);
Set(gloMonth, 4); // Example: April 2024

You should also do some other things as well, the above is just to identify the first Thursday of a specific month as a starting point. (even if you get it to work). However see if the above is enough to start and you could figure the rest of it out. If you're not able to figure the rest out, you can reply with what you tried.

Kepcla

I think I explained it wrong, but it must be the sum of the data grouped by week.

Considering that I have a database with hundreds of records from January 1st to the end of the year, with 2 columns (date and a value)

v-mengmli-msft

Hi @Kepcla ,

Are you saying that each week doesn't need to be divided by Thursday to the next Thursday, and that some weeks may have a total of 0? If so, I suggest that you start dividing the week from January 1st and setting the end of each week to Saturday. You can create a collection with one column for the start date of each week and one column for the end date of each week. This collection is then used to filter the records between the start and end dates and count the total value. You may get some records with a total value of 0 that you can keep or delete them.😀

Best regards,

Rimmon

happyume