Impossible to compare dates in a select to obtain some rows

[vramirez80]

Hi all, how are you? and Merry Christmas.

Im trying to generate a report of sales based on dates range but until now its been impossible to me to do it.

I have a table "Pedidos" where each sale has a date with column name "[Orden_Fecha]", total amount of sale and so on, I've created another table called Total_Ventas_Acum where in the same row I have columns for the the "Fecha Inicio" of the range, the "Fecha fin" of the range, "Suma total ventas", "Ventas pagadas" and "Ventas por cobrar".

Right now I can select the range of dates but for example in the column "total sales" I have the following formula:

sum(SELECT(Pedidos[Total_Neto [$]],and(Pedidos[Orden_Fecha]>=[_THISROW].[Fecha Inicio],Pedidos[Orden_Fecha]>=[_THISROW].[Fecha Inicio])))

the above formula allways gives me the error

Cannot compare List with Date in (Pedidos[Orden_Fecha] >= [_THISROW].[Fecha Inicio])

I tried by enclosing the column Pedidos[Orden_Fecha] like follows

sum(SELECT(Pedidos[Total_Neto [$]],and(date(Pedidos[Orden_Fecha])>=[_THISROW].[Fecha Inicio],date(Pedidos[Orden_Fecha])<=[_THISROW].[Fecha Inicio])))

the above inmediatly gives me the error 

SELECT has invalid inputs

so I don't know what else to do to have the filtered info.

I saw the following video on youtube

https://www.youtube.com/watch?v=zxA8O4FMsYs

but it goes to another filtered view and I need to have the filtered sum.

What can I do for solving the problem?

Thanks in advance for the help.

[Suvrutt_Gurjar]

---

@vramirez80 wrote:

sum(SELECT(Pedidos[Total_Neto [$]],and(Pedidos[Orden_Fecha]>=[_THISROW].[Fecha Inicio],Pedidos[Orden_Fecha]>=[_THISROW].[Fecha Inicio])))

the above formula allways gives me the error

Cannot compare List with Date in (Pedidos[Orden_Fecha] >= [_THISROW].[Fecha Inicio])

---

In the expression above Pedidos[Orden_Fecha] will produce a list of values and it is being compared with [_THISROW].[Fecha Inicio] which will produce a single value and hence you are getting the error.

Please try with an expression of 

SUM(SELECT(Pedidos[Total_Neto [$],

                     AND([Orden_Fecha]>=[_THISROW].[Fecha Inicio],[Orden_Fecha]<=

                                [_THISROW].[Fecha Inicio]

                               )

                            )

                    )

---

@vramirez80 wrote:

sum(SELECT(Pedidos[Total_Neto [$]],and(Pedidos[Orden_Fecha]>=[_THISROW].[Fecha Inicio],Pedidos[Orden_Fecha]>=[_THISROW].[Fecha Inicio])))

---

There seems to be another typo in your expression in that it compares both times with greater than of equal symbol(>=) in the AND() expression. I have corrected it in my suggestion.

On relook there seems to be yet another typo in your expression you seem to have incorrect additional parenthesis in [$]] column as below

---

@vramirez80 wrote:

sum(SELECT(Pedidos[Total_Neto [$]],

---

I have corrected that also in the suggested expression.

[vramirez80]

Look, I've been trying your suggestion before and it didn't worked, the column "Orden_Fecha" can't be used raw because is not part of the table "Total_Ventas_Acum" but the table "Pedidos" so I rearranged like follows

SUM(SELECT(Pedidos[Total_Neto [$]],AND(Pedidos[Orden_Fecha]>=[_THISROW].[Fecha Inicio],Pedidos[Orden_Fecha]<=[_THISROW].[Fecha Inicio])]))

and it gave me the error 

Cannot compare List with Date in (Pedidos[Orden_Fecha] >= [_THISROW].[Fecha Inicio])

again the same intent to compare list with date, so I tried by enclosing the list of dates as date()

SUM(SELECT(Pedidos[Total_Neto [$]],AND(date(Pedidos[Orden_Fecha])>=[_THISROW].[Fecha Inicio],date(Pedidos[Orden_Fecha])<=[_THISROW].[Fecha Inicio])]))

but now the expression assistant gives me the error SELECT has invalid inputs.

I taught initially that SELECT() would see the condition in a row context but apparently not.

I'll try with filter to see if works.

[Suvrutt_Gurjar]

Okay, thank you. I have nothing more to add. I may only say based on current understanding that your current expression will not work. You may also need to revisit your column [$]] again because it seems to have an extra parenthesis that I already mentioned earlier.

All the best.

[vramirez80]

thanks for the suggeston but is not extra, the column has the name "Total Neto [$]", so when the name is enclosed with [] it appears to have double closed square brackets but is not, in fact, if I remove any of the two then the expression assistant gave me an error of column not found because concatenates automatically the column name with the following text

[vramirez80]

and with filter() doesn't work, gaves me the same error

Cannot compare List with Date in (Pedidos[Orden_Fecha] >= [_THISROW].[Fecha Inicio])

[Suvrutt_Gurjar]

---

@vramirez80 wrote:

thanks for the suggeston but is not extra, the column has the name "Total Neto [$]"

---

Okay, got it. It makes sense then to use that extra parenthesis, even though I may still suggest to desist from using characters that have some other reserved meaning such as "[", "]" or "(" , ")" in expressions.

[vramirez80]

Hi all and happy new year 2025.

It happens that what I needed to know AppSheet has it enabled by default. When one press the search button then it activates the search entry and at right there is a filter button where the data can be filtered by dates among other parameters.