Forum Discussion

RvdHeijden's avatar
RvdHeijden
Icon for Post Prodigy rankPost Prodigy
7 years ago
Solved

HELP !! Sum formula with different variables

Hey Guys,   I need a formula that can calculate the following.   I have a table 'Lengtes' that has the average lengte in a certain area devided between 'Kern', 'Industrieel' and 'Buitengebied' F...
Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    the join between tables is active, 1:many and with single direction?
    you added the new column to the Adressen table, correct?

    EDIT - maybe there are some spaces/non printable characters in the Gebiedstype column?  try this code

    Average =
IF (
    Adressen[civiel gesloten] <> BLANK ();
    SWITCH (
        TRIM ( Adressen[Gebiedstype] );
        "Kern"; RELATED ( Lengtes[Gemiddelde graaflengte Kern] );
        "Buitengebied"; RELATED ( Lengtes[Gemiddelde graaflengte Buitengebied] );
        "Industrieel"; RELATED ( Lengtes[Gemiddelde graaflengte Industrieel] );
        BLANK ()
    )
)

     

     

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

is there any join between the 2 tables?
can you post here sample rows from both tables that can be copied?

  • RvdHeijden's avatar
    RvdHeijden
    Icon for Post Prodigy rankPost Prodigy
    7 years ago

    Hey Stachu, there is a relationship between the two tables based on 'DP naam'.

    Here is an excel copy/paste from the excel

     

    So basically if an adress has been 'Civiel gesloten' (<> Null) then it should look at the DP naam and Gebiedstype and check in the tabel 'Lengths' what the average length is for that DP name and Gebiedtype

     

    Table 'Adressen'

    StraatnaamHuisnummerToevoegingCiviel geslotenDP naamGebiedstype
    Zitterstraat 2 24-9-2018STAS_1E_DP035Kern
    Zitterstraat 4 18-5-2018STAS_1E_DP037Kern
    Zitterstraat 6A14-9-2018STAS_1E_DP035Buitengebied
    Zitterstraat 8 2-8-2018STAS_1E_DP035Industrieel
    Zitterstraat 10 4-9-2018STAS_1E_DP036Industrieel
    Zitterstraat 12  STAS_1E_DP037Buitengebied
    Zitterstraat 14  STAS_1E_DP037Kern
    Zitterstraat 16 8-8-2018STAS_1E_DP040Kern

     

    Tabel 'Lengths'

    DP naamAverage length KernAverage length BuitengebiedAverage length Industrieel
    STAS_1E_DP03556,0124,689,5
    STAS_1E_DP03630,063,448,6
    STAS_1E_DP03729,589,674,9
    STAS_1E_DP04014,9111,465,0
    • Stachu's avatar
      Stachu
      Icon for Community Champion rankCommunity Champion
      7 years ago

      this is syntax for calculated column

      Column = 
IF(Adressen[Civiel gesloten]<>BLANK(),
    SWITCH(Adressen[Gebiedstype],
        "Kern",RELATED(Lengtes[Average length Kern]),
        "Buitengebied", RELATED(Lengtes[Average length Buitengebied]),
        "Industrieel", RELATED(Lengtes[Average length Industrieel]),
        BLANK()
    )
)
      • RvdHeijden's avatar
        RvdHeijden
        Icon for Post Prodigy rankPost Prodigy
        7 years ago

        Hey Stachu thanks for the help so far but it doesn't work yet

         

        Ive copied the formula and altered it a bit to fit the columnames etc

         

        Average =
        IF(Adressen[civiel gesloten]<>BLANK();
        SWITCH(Adressen[Gebiedstype];
        "Kern";RELATED(Lengtes[Gemiddelde graaflengte Kern]);
        "Buitengebied"; RELATED(Lengtes[Gemiddelde graaflengte Buitengebied]);
        "Industrieel"; RELATED(Lengtes[Gemiddelde graaflengte Industrieel]);
        BLANK()
        )
        )

         

        The column however doesn't return any values, there isn't an error on it so that is good but it still doesn't return values