Chart col series or row series to show trend project cost

[DaGi07]

Hello to everyone,

I would represent this table in trend chart (col series or row series) showing the sum of column [durata] for each month and year. 

I suppose that i have to create a Vc to aggregate data for each month and year because each row is a single day

Then i would represent the project budget (example. 100k) divided for each month. The project month are 10 = 10k for each month. 

In order to have a comparison between the expenses for month and the budget for month.

TKS

[WillowMobileSys]

I am understanding that you wish to have a chart to show the trends and compare the Monthly expenses and budget.

The best way to handle this is to create an automation process that computes the Monthly values into a dedicated table.  The idea is to have a Scheduled process that generates these rows.  Then have your chart use this new Monthly reporting table as its source.  This approach will avoid the cost associated with having Virtual Columns constantly recalculating the monthly values on each and every Sync.  The downside is that you will need to implement the table and the automated process.

If you want to take the easy approach, then you can create a couple Virtual columns to compute the Monthly expenses and the Monthly Budget for each row.  Then you could use a Slice to filter the data down to just a single row for each Month and only the columns needed for the chart.  Then use this Slice as the source for the Chart view.  The downside is that you are recomputing on every Sync the Monthly values on every row.  As your app grows it will suffer in long Sync times - possibly very long Sync times.  A benefit would be if you wanted to show these Monthly values on each row in your detail views, then they are right there.

I DO NOT recommend the second approach.  It will be problematic for Sync times as your tables grow AND if you wish to extend the Monthly reporting to include additional values you would be inclined to add more Virtual Columns compounding the problem. 

[DaGi07]

tks for your answer @WillowMobileSys. would you be kind enough to explain to me how to implement the table and automate the process?
and on the other hand what expression to use in the Vc to calculate the compute the Monthly expenses and the Monthly Budget for each row.
So I can choose the most reasonable option between costs and benefits

[WillowMobileSys]

Details are highly dependent on your existing data structure.  The best I can do is provide a general outline:

1) Reporting table -  

• Create a table with the columns you need for reporting - Year, Month, Week, Day, Expenses, Budget, etc.
• Implement a Scheduled Bot with a series of steps/actions that will select the rows to be aggregated, computes the needed values and then either adds a row or updates a row.   

2)  VC expression - again depends on your data structure but the expression needs to be something like :

SUM(SELECT(Data Table[Column to Aggregate],  
           AND([Row Date] >= "<<Month Start Date>>", [Row Date] <= "<<Month End Date>>)))"

You will need a separate expression for each aggregate VC you add specifying which column is being aggregated.

[DaGi07]

@WillowMobileSys 

I noticed that scheduled event is only for deployed app 

i am trying to follow the first method. I have created another table BUDGET_VS_CONS  with these fields

This field are originated with the action "aggiungo riga" in the bot:

I have a problem:
every time I add a row in the RESOCONTO_ATTIVTA table it creates a new row in the BUDGET_VS_CONS table instead i would mantein a row for each month and in the field [costo] i would the sum the filed RESCONTO_ATTIVITA[durata]

example:

If in table RESOCONTO_ATTIVITA i have 3 rows of June 2022 month:

in table BUDGET_VS_CONS i would only one row for the month of June 2022

Then, when i'will have in table RESOCONTO_ATTIVITA multiple values with the July month i would have only one row in table BUDGET_VS_CONS

I hope you will help me because it is an important task

tks

[Steve]

---

@DaGi07 wrote:

I noticed that scheduled event is only for deployed app

---

Correct! For a scheduled event to occur, the app must be deployed.

[DaGi07]

@Steve the deployment of this app depend also to this possibility to manage project cost showing the trend across the time... 

maybe it's possible set a condition to create e new row? (a logic expression to create a row only when is the next month or similar)

[DaGi07]

hello @WillowMobileSys , I'm revisiting this post because I'm developing a similar app for my work and I'm faced with the same situation. Could you explain to me how to create a slice that allows you to filter down to a single row as you told me here "Then you could use a Slice to filter the data down to just a single row for each Month and only the columns needed for the chart".  

thanks you 🙏

[WillowMobileSys]

Knowing what data you want to filter would help...but the assumption is that you have summary rows by Year and Month - one for each Month.

How you filter depends on your needs.  If you wanted to show the summary for LAST month then in your Slice on your Summary table, you could use an expression like this:

IF(MONTH(TODAY()) = 1,
   AND([Year] = YEAR(TODAY()) - 1, [Month] = 12,
   AND([Year] = YEAR(TODAY()), [Month] = MONTH(TODAY()) - 1
)

This expression handles crossing the Years DEC to Jan as well as the rest of the Months

I hope this helps!.

[DaGi07]

@WillowMobileSysin order to be more precise about the data,  on the left the table with data and on the rigt the slice that i would show in chart. As you can see, in the slice there is the sum of cost for each month (jan 2024 and feb 2024)

tks for your support!

[WillowMobileSys · Accepted solution]

I would recommend separating Month and Year. 

Slices do not create on-the-fly datasets for you - they cannot spit out a set of summarized rows.  Instead, you will need to create an EXPLICIT "Summary" table that has the data summed as you are indicating as "Slice" in your example.  This does mean creating some process that takes the rows from your "Table" and performs the necessary calcs to create or update rows in the "Summary" table.  Then you either use that table directly in the chart OR use a Slice to filter down to only the summary rows the chart needs.

I hope this helps!!

[DaGi07]

@Steve @WillowMobileSys do you have any idea what kind of expression can i put in condition before firing the action? 

tks

[WillowMobileSys]

The condition to run the Bot should use the new row to determine if there exists a BUDGET_VS_CONS table row based on the Month and Year. 

Assuming you have a Year and Month column in the BUDGET_VS_CONS table, the expression to do this should be something like:

COUNT(SELECT(BUDGET_VS_CONS[Row ID], 
      AND([Year] = YEAR([_THISROW].[Data]), 
          [Month] = MONTH([_THISROW].[Data])))) = 0

[DaGi07]

tks @WillowMobileSys  i used this expressions:

COUNT(SELECT(BUDGET_VS_CONS[ID],
AND(YEAR([Periodo]) = YEAR([_THISROW].[data]),
MONTH([Periodo]) = MONTH([_THISROW].[data])))) = 0

but it's not complete because i would create e new row also only there isn't yet the project (if in table BUDGET_VS_CONS there is the project "XY" the row shouldn't be create, on the contray if the project "XY" there isn't i want create a new row.)

I refresh you the structure of table RESOCONTO_ATTIVITA where the bot is based

where the filed [conto_analitico] is the project.

I spent much time to find the correct condition to add your but I didn't succeed 

tks so much!!! 🙏

[WillowMobileSys]

I am not sure if I am clear.  Are you still looking for a solution?

I am understanding that you want to create a BUDGET_VS_CONS row whether there is a Project or not.  And I understand that you are trying to do this without deploying the app - meaning you do not have Scheduled Bots to perform this for you.

However, to run all other Bots, there needs to be some kind of user activity to add, edit or delete a row.  You can then use this activity to trigger a Bot and do pretty much anything you need to do.

If you are NOT creating a Project row but still want the Monthly BUDGET_VS_CONS row to be inserted, then you need to identify some other user activity to tie into to run a bot and create your row.

By the way, it doesn't have to be a Bot.  You can use actions attached to user activity to add rows as well.  The main point is that yo have to find some appropriate current user activity within the app to use as a trigger to create your budget row.

[DaGi07]

hi @WillowMobileSys , 
I took up this topic again because it is important for my app.
As you can see, in the RESOCONTO_ATTIVITA' table for the same date there are more projects (these data have been added with import csv). In the BUDGET_VS_CONS table I would like to see the projects taken only once.

In this screen is showed the result after the execution of the bot

The condition you advised me is based only on the period and not also on the project

TKS