Combining if into set analysis

[MikeJones]

Hi all, below is a snapshot of my data which starts in Jan 1st 2023.  I wish find total using set analysis with the following criteria:

1. If(Actual > Planned, Actual, Planned) and

2. Between Monday and Friday.  Therefore, the correct total planned would be 11.

I don't wish to create a new variable or using the load editor.

Jobid	Date	Day	Planned Widgets	Actual Widgets	if Actual > Planned,Actual,Planned
1	18-Mar-24	Mon	0	1	1
2	19-Mar-24	Tue	2	2	2
3	20-Mar-24	Wed	2	1	2
4	21-Mar-24	Thu	1	1	1
5	21-Mar-24	Thu	0	1	1
6	22-Mar-24	Fri	2	2	2
7	22-Mar-24	Fri	2	2	2
8	23-Mar-24	Sat	2	2	2
9	24-Mar-24	Sun	2	2	2
			13	14	15
		Mon to Fri	9	10	11

[QFabian]

Dear @MikeJones , here an example, hope it works for you.

About 'Day' field, i use it as text, it could be a number, creating it from Date using num(weekday(Date))

Script:

Aux:
Load * INLINE [
Jobid, Date, Day, Planned Widgets, Actual Widgets
1, 18-3-24, Mon, 0, 1, 1
2, 19-3-24, Tue, 2, 2, 2
3, 20-3-24, Wed, 2, 1, 2
4, 21-3-24, Thu, 1, 1, 1
5, 21-3-24, Thu, 0, 1, 1
6, 22-3-24, Fri, 2, 2, 2
7, 22-3-24, Fri, 2, 2, 2
8, 23-3-24, Sat, 2, 2, 2
9, 24-3-24, Sun, 2, 2, 2
];

Table :

formula :

{< Day = {'Mon', 'Tue', 'Wed', 'Thu', 'Fri'} >}   //set analysis over the aggregation sum()
sum(If([Actual Widgets] > [Planned Widgets], [Actual Widgets], [Planned Widgets]) ) //sum with if inside

[MarcoWedel]

you could also shorten the If()-function with a RangeMax()

e.g.

Sum({$<Day={"<5"}>} RangeMax(PlannedWidgets,ActualWidgets))

table1:
Load Jobid,
     Date#(Date,'DD-MMM-YY') as Date,
     WeekDay(Date#(Date,'DD-MMM-YY'),0) as Day,
     PlannedWidgets,
     ActualWidgets
Inline [
Jobid	Date	PlannedWidgets	ActualWidgets
1	18-Mar-24	0	1
2	19-Mar-24	2	2
3	20-Mar-24	2	1
4	21-Mar-24	1	1
5	21-Mar-24	0	1
6	22-Mar-24	2	2
7	22-Mar-24	2	2
8	23-Mar-24	2	2
9	24-Mar-24	2	2
] (delimiter is '\t');