Hi there,
I need help.
I intend a view that shows total income and total expenses, using a preview where I set the filter by date.
I have a table called : Cash Control. In this table, the columns: Type (Incomes or Expenses), Amount and Timestamp. I added two virtual columns called: Total Incomes and Total Expensive. I used this expresion to calculte total Incomes and other similar to Total Expensive:
Sum(
SELECT
(
CashControl[Amount],
[TypeID] = "Incomes"
)
)
View: Filter
View: Total
The case: Every time that I set date, the totals dont change, always it keep the according sum of incomes and expensive of the data in the column
I appreciate any suggestions you can share.
Confirm what data the Total view is based on. Somewhere, you need to reference the selected dates in filtering the data.
Hi dbaum,
Yes I did it
I have a behavior, this one has a references to Timestamp.
@HguillenA wrote:
I have a behavior, this one has a references to Timestamp.
I don't understand.
I suggest share screenshots of configuration of your Total view and the columns in the slice or table that view is based on.
Hi dbaun,
1. Table1: CashControl with virtual columns: Total Incomes, Total Expensive.
This table has TypeID (user can select Incomes or Expensives), amount and timestamp
2. View: Cash Balance. This is main goal. I have other view, I use it to set range date and I need that the Total Incomes and Expensives change according range date selected
3. Table2: Date Range. With this table I have a Behavior
4. Behavior.
Target:
LINKTOFILTEREDVIEW("Cash Balance",AND(DATE([Timestamp])>=[From],DATE([Timestamp])<=[To]))
Note: Cash Balance is the view with the totals
5. I use the bejavior in a view called Balance. In this view I set the range date
What is my case? when I set any range date. Total Incomes and Expensive is always the same value: sum of all data according with the criteria, no filter the date
Thank you for your time
If the columns displayed in #2 are the columns you describe in #1, that's where your issue is. The expression you show in #1 sums all rows. You can change that expression to instead sum only rows within the selected date range.
FYI: The LINKTOFILTEREDVIEW() function only filters which rows are displayed in a view--not which rows are referenced in the table's expressions.
Hi dbaum,
Thank you for your time.
I dont get it.
I did a test. In view of 2, I removed the columns (Total Incomes and Expensive) and added others from the same table, Example: timestamp. I changed the view type to Table. In this way, the data is filtered according to the date range that you want to configure
What topic can I study to do what you suggest?
"You can change that expression to instead sum only rows within the selected date range."
@HguillenA wrote:
What topic can I study to do what you suggest?
As @daviddlaa suggests, see SELECT.
Hi, please share this project, too me. Thank you!😍
saludos:
utiliza otra tabla con las marcas de fechas:
luego haga una colunma virtual y en ella pon la siguiente formula:
sum(select(CashControl[Cantidad], and([fecha de cash control]>=[_thisrow].[fecha de filtro],[fecha de cash control]<=[fecha de filtro])))
es lo que yo haria, luego haga lo mismo para sumar los ingresos:
sum(select(CashControl[ingresos], and([fecha de cash control]>=[_thisrow].[fecha de filtro],[fecha de cash control]<=[fecha de filtro])))
eso me ha dado resutados, confirme si se pudo, saludos desde Ecuador!!!
Gracias daviddlaa,
Voy a probar
daviddlaa,
Hice varias pruebas atendiendo su propuesta. pero no lo logre resolver:
1. La tabla que sufieres hacer. Es una tabla para Filtros de fecha con una columna de fecha inicial y fecha final?
2. Desde esa tabla, hacer las columnas virtuales "llamando" los datos de la tabla cash control?
1. corrijo la formula :
sum(select( CashControl[Cantidad], and([fecha de control de efectivo]>=[_thisrow].[fecha de filtro de inicio],[fecha de control de efectivo]<=[fecha de filtro final]) ))
sum(select( CashControl[ingresos], and([fecha de control de efectivo]>=[_thisrow].[fecha de filtro de inicio],[fecha de control de efectivo]<=[fecha de filtro final]) ))
2. esta columna virtual la tienes de crear desde la tabla del filtro que ya la tienes creada.
User | Count |
---|---|
26 | |
25 | |
25 | |
22 | |
22 |