Create monthly totals from table with daily data

[Vera]

Hi all,

I am trying to create monthly totals for a table with daily data, and my attempts so far have not led to success. Does anyone know how to do the following?

The table has a date column, with consecutive dates for four years, a column time t starting at 0, and (as a total from several other columns) the column total users.

Based on the column total users I managed to create a column new users which is the increment in users at time t compared to time t-1, with the following formula:

*If(thisRow.[time t]=0 ,thisRow.[users-total], *

(thisRow.[users-total]-
(Filter(thisTable, [time t]=thisRow.[time t]-1).[users-total]
)
)
)

Now, I would like to be able to create a column, either in a separate table or the same table, that shows the monthly increments in users.

I’ve been trying to find a way to add up the new users column per month/year, or, alternatively to subtract total users at the start of each month from total users at the end of each month (user total does not decline at any point, it is always increasing).

The closest I’ve come is the following, but it is slightly off because the formula can only work with every month having 31 (or every month having 30) days:
(thisRow.[users-total]-
(Filter(thisTable,
[time t]=thisRow.[time t]-31).[users-total]
)
)

Any help and ideas are appreciated, thank you!

[Jean_Pierre_Traets]

Dear @Vera

Sorry for the short reply, does this dummy doc put you in the right direction.
If not, you could post your doc (delete sensitive content and make it as small as possible), in this way I am sure you will get the proper support to find out the logic to solve your challange.

Success,
//JP

[Vera]

Dear @Jean_Pierre_Traets

Thank you for your reply!
I’ve considered the solution in your dummy - however, I want to be able to make further calculations based on the monthly totals, and ideally have a view of the table that does not show all days of the month, but the monthly totals only, for example by filtering out the first day of the month.

I’ve made a small example doc to illustrate what I am trying to do:

[Paul_Danyliuk]

Won’t simply grouping by month/year and then collapsing those groups work?

Otherwise, it’s possible to do some formula magic to get the aggregation you want. If simply grouping and collapsing don’t work for you, I can help with the formula.

[Vera]

@Paul_Danyliuk thank you for your reply! Is it possible to use those totals in a formula for another column?

[mallika]

Summary/Aggregate Views

We often get asked about creating an aggregate or summary table for a tables with multiple rows. These are two ways I like to do it. Would love to hear about other ways you do it or places where you have used something like this. Base Table- [19%20AM] Using Grouping to create the summary/aggregate view (Filtered with a control so it is easy to change the view based on date ranges - [57%20AM] Using a filter formula in a new table. This is useful when you want data from multiple table or wa…

[Paul_Danyliuk]

You can write a formula like thisTable.Filter(Month = thisRow.Month && Year = thisRow.Year).Users.Sum() to select rows that match the same month and year and calculate a sum/max/whatever over those.

I added a column in your doc in a Section 2 table with such formula.

P.S. it seems like users total and new users have some sort of discrepancy? See in spots like this:

[Vera]

@Paul_Danyliuk that is exactly what I needed, I couldn’t get the formula to work so wasn’t sure it was possible. Working perfectly now, thank you!