Help needed: How to find matching products

[StaniP]

Dear all, 
I want to use a part of my App to find matching products with their pricing based on the dimensions.
For products with straightforward dimensions it works without a problem.


However, some products are not that easy to handle so that the value for PROJECTION has to be first calculated with MIN(), MAX() and ABS().

It works, but only some times and pretty random.
For example here - three results should have been correct.

If anyone has an idea what I could have done wrong, it would be highly appreciated, since I am stuck.


Here are the MIN, MAX, ABS formulas I use 

IF(
  AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "Bioclimatic" 
    
  ),
  MIN(
    SELECT(
      MEASAUREMENTS[PROJECTIONS BIOCLIMATIC LAMDA],
      [PROJECTIONS BIOCLIMATIC LAMDA] >= [_THISROW].[PROJECTION]
    )
  ),
  IF(
    AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "Bioclimatic"
    ),
    MIN(
      SELECT(
        MEASAUREMENTS[PROJECTIONS OPEN ROOF],
        [PROJECTIONS OPEN ROOF] >= [_THISROW].[PROJECTION]
      )
    ),
    IF(
      AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "Bioclimatic"
        
      ),
      MIN(
        SELECT(
          MEASAUREMENTS[PROJECTIONS OPEN SKY],
          [PROJECTIONS OPEN SKY] >= [_THISROW].[PROJECTION]
        )
      ),
      IF(
        AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "CASSETTE"
        ),
        MIN(
          SELECT(
            MEASAUREMENTS[PROJECTIONS CASSETTE 530],
            [PROJECTIONS CASSETTE 530] >= [_THISROW].[PROJECTION]
          )
        ),
        IF(
            AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "CASSETTE"
          ),
          MIN(
            SELECT(
              MEASAUREMENTS[PROJECTIONS CASSETTE 732],
              [PROJECTIONS CASSETTE 732] >= [_THISROW].[PROJECTION]
            )
          ),
          IF(
               AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "CASSETTE"
            ),
            MIN(
              SELECT(
                MEASAUREMENTS[PROJECTIONS CASSETTE EPIC],
                [PROJECTIONS CASSETTE EPIC] >= [_THISROW].[PROJECTION]
              )
            ),
            IF(
                 AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "CASSETTE"
              ),
              MIN(
                SELECT(
                  MEASAUREMENTS[PROJECTIONS MONOBLOCK],
                  [PROJECTIONS MONOBLOCK] >= [_THISROW].[PROJECTION]
                )
              ),
              IF(
                  AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "ZIP | SCREEN"
                ),
                MIN(
                  SELECT(
                    MEASAUREMENTS[PROJECTIONS ZIP50],
                    [PROJECTIONS ZIP50] >= [_THISROW].[PROJECTION]
                  )
                ),
                IF(
                   AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "ZIP | SCREEN"
                  ),
                  MIN(
                    SELECT(
                      MEASAUREMENTS[PROJECTIONS ZIP60],
                      [PROJECTIONS ZIP60] >= [_THISROW].[PROJECTION]
                    )
                  ),
                  IF(
                     AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "ZIP | SCREEN"
                    ),
                    MIN(
                      SELECT(
                        MEASAUREMENTS[PROJECTIONS SCREEN Φ4mm],
                        [PROJECTIONS SCREEN Φ4mm] >= [_THISROW].[PROJECTION]
                      )
                    ),
                    IF(
                      AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "ZIP | SCREEN"
                      ),
                      MIN(
                        SELECT(
                          MEASAUREMENTS[PROJECTIONS SCREEN Φ10mm],
                          [PROJECTIONS SCREEN Φ10mm] >= [_THISROW].[PROJECTION]
                        )
                      ),
                      IF(
                        AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "ZIP | SCREEN"
                        ),
                        MIN(
                          SELECT(
                            MEASAUREMENTS[PROJECTIONS RIDEAU HD],
                            [PROJECTIONS RIDEAU HD] >= [_THISROW].[PROJECTION]
                          )
                        ),
                        ""
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  )
)

IF(
  AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "Bioclimatic" 
  ),
  MAX(
    SELECT(
      MEASAUREMENTS[PROJECTIONS BIOCLIMATIC LAMDA],
      [PROJECTIONS BIOCLIMATIC LAMDA] >= [_THISROW].[PROJECTION]
    )
  ),
  IF(
    AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "Bioclimatic"
    ),
    MAX(
      SELECT(
        MEASAUREMENTS[PROJECTIONS OPEN ROOF],
        [PROJECTIONS OPEN ROOF] >= [_THISROW].[PROJECTION]
      )
    ),
    IF(
      AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "Bioclimatic"
      ),
      MAX(
        SELECT(
          MEASAUREMENTS[PROJECTIONS OPEN SKY],
          [PROJECTIONS OPEN SKY] >= [_THISROW].[PROJECTION]
        )
      ),
      IF(
        AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "CASSETTE"
        ),
        MAX(
          SELECT(
            MEASAUREMENTS[PROJECTIONS CASSETTE 530],
            [PROJECTIONS CASSETTE 530] >= [_THISROW].[PROJECTION]
          )
        ),
        IF(
            AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "CASSETTE"
          ),
          MAX(
            SELECT(
              MEASAUREMENTS[PROJECTIONS CASSETTE 732],
              [PROJECTIONS CASSETTE 732] >= [_THISROW].[PROJECTION]
            )
          ),
          IF(
               AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "CASSETTE"
            ),
            MAX(
              SELECT(
                MEASAUREMENTS[PROJECTIONS CASSETTE EPIC],
                [PROJECTIONS CASSETTE EPIC] >= [_THISROW].[PROJECTION]
              )
            ),
            IF(
                 AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "CASSETTE"
              ),
              MAX(
                SELECT(
                  MEASAUREMENTS[PROJECTIONS MONOBLOCK],
                  [PROJECTIONS MONOBLOCK] >= [_THISROW].[PROJECTION]
                )
              ),
              IF(
                  AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "ZIP | SCREEN"
                ),
                MAX(
                  SELECT(
                    MEASAUREMENTS[PROJECTIONS ZIP50],
                    [PROJECTIONS ZIP50] >= [_THISROW].[PROJECTION]
                  )
                ),
                IF(
                   AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "ZIP | SCREEN"
                  ),
                  MAX(
                    SELECT(
                      MEASAUREMENTS[PROJECTIONS ZIP60],
                      [PROJECTIONS ZIP60] >= [_THISROW].[PROJECTION]
                    )
                  ),
                  IF(
                     AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "ZIP | SCREEN"
                    ),
                    MAX(
                      SELECT(
                        MEASAUREMENTS[PROJECTIONS SCREEN Φ4mm],
                        [PROJECTIONS SCREEN Φ4mm] >= [_THISROW].[PROJECTION]
                      )
                    ),
                    IF(
                      AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "ZIP | SCREEN"
                      ),
                      MAX(
                        SELECT(
                          MEASAUREMENTS[PROJECTIONS SCREEN Φ10mm],
                          [PROJECTIONS SCREEN Φ10mm] >= [_THISROW].[PROJECTION]
                        )
                      ),
                      IF(
                        AND(
    ISBLANK([Suitable Model]) = TRUE, 
    [PRODUCT CATEGORY] = "ZIP | SCREEN"
                        ),
                        MAX(
                          SELECT(
                            MEASAUREMENTS[PROJECTIONS RIDEAU HD],
                            [PROJECTIONS RIDEAU HD] >= [_THISROW].[PROJECTION]
                          )
                        ),
                        ""
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  )
)

IF(
  ABS([Greek Projection Lower Value] - [_THISROW].[PROJECTION]) <= ABS([Greek Projection Higher Value] - [_THISROW].[PROJECTION]),
  [Greek Projection Lower Value],
  [Greek Projection Higher Value]
)

[Tijesuni]

Hello @StaniP 

It looks like you're trying to match products based on dimensions but are experiencing inconsistent results when using MIN(), MAX(), and ABS(). Here are a few possible reasons and solutions:

Potential Issues & Solutions

1. Order of Operations

   • Ensure that MIN() and MAX() are applied correctly to compare dimensions in a consistent manner.
   • If you're checking two values (e.g., width vs. height), ensure they are always compared in the same order.

2. Data Formatting Issues

   • Check whether all dimensions are stored as numbers, not text.
   • Ensure there are no extra spaces or formatting inconsistencies.

3. Rounding Errors

   • If you’re dealing with floating-point numbers, minor decimal differences might cause inconsistencies.
   • Use ROUND(value, X) to standardize the comparison.

4. Incorrect Use of ABS()

   • ABS() returns the absolute value, but if you're comparing differences, ensure that it’s applied correctly and consistently across the calculation.

5. Conditional Matching Rules

   • If some products don’t match correctly, there might be an issue with how the conditions are written.
   • Check if all necessary conditions are included when using MIN(), MAX(), and ABS().