I have a table where sales (operations) are recorded. In each entry, I record the date, and I also have a column of type math(date) that puts the month number.
Then I have another table called dashboard. In it, I have a column of type joined list where all the months are listed like this:
12,12,01,05,10…
What I need is a column that counts the number of times the number 12 appears (for December) and likewise for all the months. That way, I can draw a report of how many sales there are per month.
How can I do it?
Change your joined list to an array using a split text column. Relate that to your month numbers. Do a rollup through that relation.
How can I count the number of time that each number appears in this column?
Just Multiple Relation from month to itself and count thru this relation
but I need to count for each month not total of all months
I need sometime like this:
Count where Month = 12
Then I will get the total of operations of december
It exactly gives to you count of December.
And why you don’t follow Darrens suggestion?
Usually, i do like this:
I have Dictionary table of months
In this table i have multiple relation to the operations table
and i have rollup count thru this relation.
In a dashboard i have collection sourced from month dictionary table and filtered if month is included in lookup from operation table
So, i have only month which appeared in operations also i have count of month’s operations.
1 Like
How do you relate the operations table to the dictionary?
This is my dictionary table
Create Multiple relation from Month number to operation table in which math column: Month(operationDate)
Usually i creating not only Operation date column also i write Math Year(NOW)*100+Month(NOW) and later use it for addressing YEARMONTH 202403 also you can write Month(now).
Relation to Numeric Month or Numeric YEARMONTH much more faster, compared to relation to Calculated column.
Great!
But how I filter this by dates (From and to)
The “Operaciones por mes” Chart is the one for the operations by month
Use query for source for Total QUERY with operation date is on of after start and operation date is on or before End
But usually, i use YYYYMMDD as Numeric from Math Year(opDate)*10000+Month(opDate)*100+Day(opDate) instead common Date.
Oh, no, for total of course.
If you want to filter Operation per month according start and end date and keep looks, so you need use Query in DICT Month Table instead relation and add to it dates filter. But i little bit misunderstand reason to filter Operation per month chart by dates…
