Struggling with a simple counting in a table

[Andy_Farquharson1]

Thanks always for your help here.

I am gathering daily data in a table, tracking actions and results and the week it happened. I then would like to create a week summary, which provides a count/sum for the week, dynamically adding a new row when a new week is present in the daily table.

I can’t seem to get a countif or sum to work, am I approach this the right way?

[Tamerlan_PRO]

Hi @Andy_Farquharson1 ! I took the liberty of making some changes to your document to demonstrate a potential solution based on my understanding of your task:

1. For the results table, I updated the formula in the Actual property to:

   [Daily Data].Filter([Week Number]=thisRow.Week).Person.Sum()
   

   This calculates the total number of people for each week.

2. Adjusted the formula in Gap to swap the values and display the actual difference more accurately.

    thisRow.Actual-thisRow.Goal

3. Added a button that allows you to instantly add new weeks to the results table:

   [Daily Data].[Week Number].Unique().ForEach(AddOrModifyRows(Table, Week=CurrentValue, Week, CurrentValue))
   

4. Created an automation that triggers the button on a schedule to fully automate the process.
   

   

Could you confirm if this solution aligns with what you were aiming to implement?

[Andy_Farquharson1]

This is perfect, thank you so much! THis works perfectly!

The formula for the buttton looks a little complicated, can you please explain the functionality?

[Tamerlan_PRO]

Hi @Andy_Farquharson1! I’m glad the solution worked for you. Below is a detailed explanation of how the formula in the button works:

[Daily Data].[Week Number].Unique().ForEach(
  AddOrModifyRows(Results, Week=CurrentValue, Week, CurrentValue)
)

1. [Daily Data].[Week Number]
   This part of the formula refers to the Week Number column in the Daily Data table. This column contains the week numbers that we want to use for creating or updating rows.

2. .Unique()
   The Unique() method returns a list of unique values from the Week Number column.
   For example, if Week Number contains [1, 1, 2, 3, 3], the result will be [1, 2, 3].

3. .ForEach(...)
   The ForEach() method iterates over each element in the list generated by .Unique().
   In this case, CurrentValue will take on each value from the list [1, 2, 3] during the iteration. CurrentValue represents the current item being processed from the list.

4. AddOrModifyRows(Results, Week=CurrentValue, Week, CurrentValue)
   This method either adds a new row to the specified table (Results) or modifies an existing row:

   • Results — the table where rows will be added or updated.
   • Week=CurrentValue — the condition for finding existing rows. If a row with this week number already exists, it will be updated.
   • Week — the column that will be updated or created.
   • CurrentValue — the value that will be written to the Week column.

How it works together:

1. The formula retrieves all unique week numbers from Daily Data.[Week Number].
2. For each week number in the list:
   • If a row with this week number already exists in Results, it is updated.
   • If no row with this week number exists, a new row is created.

Example:

Let’s assume:

• The Week Number column in Daily Data contains [1, 1, 2, 3].
• The Results is initially empty.

After the formula is executed:

1. Unique week numbers: [1, 2, 3].
2. The formula processes these values sequentially:
   • Creates a row with Week = 1.
   • Creates a row with Week = 2.
   • Creates a row with Week = 3.

If a row with Week = 1 already exists in Results, it will simply be updated instead of being recreated.