related child in weeknum

[Daniel_Bennett]

Hi all,

I have got myself stuck.

view sorted based on wk number and bay date ascending for creating headers for the week. This worked perfectly until I have added the last  part?? it no longer keeps the week in groups and has multiple entries with the same wk number. any help much appreciated

before:

after:

WEEKNUM([_THISROW].[bay date]) & " Week " & "

(SASHS " &
SUM(SELECT(
first[sashs],
and(WEEKNUM([bay date]) = WEEKNUM([_THISROW].[bay date]),[job state]<>"Complete",[full payment]<>"Hold"))) & ")

(SASHS MANUAL " & SUM(SELECT(first[sashs manual],and(WEEKNUM([bay date]) = WEEKNUM([_THISROW].[bay date]),[job state]<>"Complete",[full payment]<>"Hold"))) & ")

(BOM " & SUM(SELECT(first[BOM],and(WEEKNUM([bay date]) = WEEKNUM([_THISROW].[bay date]),[job state]<>"Complete",[full payment]<>"Hold"))) & ")

(BOXs " & SUM(SELECT(first[box],and(WEEKNUM([bay date]) = WEEKNUM([_THISROW].[bay date]),[job state]<>"Complete",[full payment]<>"Hold"))) & ")

(FRAMES " & SUM(SELECT(first[frame],and(WEEKNUM([bay date]) = WEEKNUM([_THISROW].[bay date]),[job state]<>"Complete",[full payment]<>"Hold"))) & ")

(1p " & SUM(SELECT(sash[1/1],AND([job state]<>"Complete",[full payment]<>"Hold",[job id] = [_THISROW].[job id],WEEKNUM([bay date]) = WEEKNUM([_THISROW].[bay date])))) & ")"

[Marc_Dillon]

In the "After" photo, you have a "3" at the very end of the 2nd week17/week18, instead of the "0" that's in the first week17/18 instance.

[Daniel_Bennett]

Hi Marc,

thank you for your comment. Yes you are correct there is a 3 in week17, it looks to me as if my calculation needs to be based on the week sum() but is performing individual sum().  any suggestions? 

[Marc_Dillon]

I was telling you why your groups were separated, because the values were different. I guess I don't understand what you're asking. What are you trying to calculate?

[Daniel_Bennett]

Apologies Marc and thanks for your time.

I'm trying to calculate table sash column "1/1" this has a reference to table first. I am looking for the calculation for a sum() Based on wknumber for column 1/1

Table first has a date column and is used as my main table but table 1/1 doesn't. 

Many thanks 

[Marc_Dillon]

I'd guess it'd have to do with the job_id condition.

Also, the very first part of your expression: 
WEEKNUM([_THISROW].[bay date]) & " Week " & "
remove the '[_THISROW].' part.

[Daniel_Bennett]

Thanks Marc,

could you please explain your meaning of job_id condition please

[Marc_Dillon]

.

[Daniel_Bennett]

Hi Marc,

I totally agree with you it is coming from that condition, do you know of a different one I could look into.

many thanks 

[Marc_Dillon]

You agree? Does that mean you tested it and confirmed that it's the job_id condition that's producing the different results?

I don't know how I would know another condition to use, I don't know your business logic.
You either need that condition, or you don't, or you need another one. Only you can make that decision.

[Daniel_Bennett]

Hi Marc,

Yes I have tested it, if I change the condition to sum all I get all headers where I need them but obviously the wrong output. 

What I'm looking for Is a condition to extract the table sash column 1/1 based of a week number to sum each week. My main table first has a reference to table sash that contains column baydate. I would like this date to be used with weeknum() giving the week number to extract, all of table sash column 1/1 need to be grouped into weeks from the job_id used as a key on both first and sash table.

It would appear I'm making a condition based on each row with job Id = this row job id. Trying desperately to get this to sum a week not every item individually.

I appreciate your time 

[Marc_Dillon]

"obviously"....

I think you need to show your data.

[Daniel_Bennett]

Hi Marc,

table first 

column [job id] as key, [baydate] as date and [related sashs] as list REF_ROWS("sash", "job id")

table sash

column [job id] key and [1/1] as number

my idea is using first[baydate] to create week numbers using weeknum(). then getting all the first[job id] into corresponding week groups and performing a sum() on sash[1/1] to generate a sum for a week so every entry in the week returns the same sum(). any suggestions

many thanks

[Daniel_Bennett]

table first

table sash

[Daniel_Bennett]

view