How to sum the data of last week?

[My_Rebecca]

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])

[Digvijay_Singh]

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

];

[Luis4 · Accepted solution]

Hola My_Rebecca.

Puedes usar la siguiente expresión para obtener el rango de fechas de la última semana.

PREVIEW

=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.

//get the maximum date

maxdate:

load

max(pk_Fecha) as MaxDate

resident Calendario;

//We store the maximum date in a variable

let vMaxDate= peek('MaxDate',0,'maxdate');

//We delete the table that is no longer needed

drop table maxdate;

Flag_Last_Week:

load

pk_Fecha,

if(pk_Fecha 

>= 

WeekStart(Max($(vMaxDate)))- 6

  and pk_Fecha 

  <= 

  Max($(vMaxDate)), 1

   ) as FlagLastWeek //We marked with 1 the last week

resident Calendario Group by pk_Fecha;

Y la expresión la simplificariamos asi.

Sum({<FlagLastWeek = {1}>} Field)

Es un poco más largo el proceso, pero más optimo.

[My_Rebecca]

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])))

[Lech_Miszkiewicz]

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