I am not sure what the downstream ramifications of this may be,
but for the purposes of the getting the All Target lines,
you could try instead a calculated field of :
IF WINDOW_MAX(MAX([Region]))<>WINDOW_MIN(MIN([Region])) THEN
IF ATTR([Sub-Category]) = 'Bookcases' THEN 200
ELSEIF ATTR([Sub-Category]) = 'Chairs' THEN 400
ELSEIF ATTR([Sub-Category]) = 'Furnishings' THEN 600
ELSEIF ATTR([Sub-Category]) = 'Tables' THEN 300
ELSEIF ATTR([Region])='Central' THEN
IF ATTR([Sub-Category]) = 'Bookcases' THEN 30
What the top part is doing is saying that in the case when All is selected,
the max region and the min region won't be the same, so it should return
the prescribed value.
Please see the workbook v10.4 attached in the Forum thread.
276943target.twbx 439.5 KB
This is doing exactly what I need. I am going to look more into Window functions so I can understand it better.
Do you have concerns about the downstream ramifications? Or just unsure?
Thank you so much!
Glad it worked for you.
Regarding the calculation, you can see a bit of how it works on Sheet 3.
My understanding of it is that we want to look over the entire sheet if
the maximum Region is different from the minimum Region, which is
our indicator that All has been selected.
The use of WINDOW_MAX here is to specify that we want to look down
the entire sheet. If we had just used MAX(Region)<>MIN(Region), it
would have done the comparison on each individual row, and would
have failed because each row only has one Region.
It looks like there are other ways to determine if All has been selected:
With regards to downstream effects, I wasn't sure if your true workbook
had other things going on that sheet, or if it was pretty much similar to the example
you had shared. If it is like the example, then I think it should be OK.
All the best.
276943target_v10.4.twbx 434.8 KB
Luckily the true workbook was very similar so there were no other issues.
This explanation and extra was really helpful too. Thanks again!