Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
As asked, I need to sum the total data of last week. How to write the expression?
sum({<[SFDSC GR Date]<=({"$(=Date(today()))"}-weekday({"$(=Date(today()))"}))>,[SFDSC GR Date]>=({"$(=Date(today()))"}-weekday({"$(=Date(today()))"})-7)>}[SFDSC GR Total Arrival line])
Hola My_Rebecca.
Puedes usar la siguiente expresión para obtener el rango de fechas de la última semana.
=Sum({<pk_Fecha = {">=$(=Date(WeekStart(Max(FieldDate))-7))<=$(=Date(Max(FieldDate)))"}>} field)
Otra opción seria realizar una flag en el script, puede ser creando una nueva tabla o en la tabla calendario.
May be try something like this -
Last week - Sum({<Date={"<=$(=Weekend(today(1),-1))>=$(=Weekstart(today(1),-1))"}>}Value)
This week - Sum({<Date={">=$(=Weekstart(today(1)))"}>}Value)
Data:
Load * inline [
Date, Value
06/01/2023, 10
06/02/2023, 10
06/03/2023, 10
06/04/2023, 10
06/05/2023, 10
06/06/2023, 10
06/07/2023, 10
06/08/2023, 10
];
Hola My_Rebecca.
Puedes usar la siguiente expresión para obtener el rango de fechas de la última semana.
=Sum({<pk_Fecha = {">=$(=Date(WeekStart(Max(FieldDate))-7))<=$(=Date(Max(FieldDate)))"}>} field)
Otra opción seria realizar una flag en el script, puede ser creando una nueva tabla o en la tabla calendario.
dear @Luis4 , thanks. I also try another way and succeed.
sum(if([SFDSC GR Date]>=today()-weekday(today()),null(),if([SFDSC GR Date]<today()-weekday(today())-6,null(),[SFDSC GR Total Arrival line])))
That syntax with :"IF() " statement is rather poor and it is well known as "performance killer". It may be fine if you are working on very small data but normally we would avoid it. You are much better of using set analysis like others suggested.
cheers