Forum Discussion

BachFel's avatar
BachFel
Icon for Helper II rankHelper II
8 years ago
Solved

Calculate, sum? need help with a formula

Following problem: in the table LN[Leistungsbeschreibung] can be more than one row with the text "Objektbegehung", per ID. i.E.:   ID          Leistungsbeschreibung       Datum  111        Objek...
Need Help
  • Floriankx's avatar
    Floriankx
    8 years ago

    Hello,

     

    I used ID instead of KostElementID, might be a little confusing because you have several IDs

     

    FILTER(LN;LN[KostElementID]=EARLIER(KontElement[KostElementID]) hopefully works now.

BachFel's avatar
BachFel
Icon for Helper II rankHelper II
8 years ago
Floriankx wrote:

Hello,

 

you're right. Formula was incomplete:

 

=CALCULATE(MIN(LN[Datum]);FILTER(LN;LN[Leistung...]="Objektbegehung");FILTER(LN;LN[ID]=EARLIER(KontElement[ID])))

 

Mit freundlichen Grüßen

Best regards

 

Hello,

 

I didn´t get it.

Until =CALCULATE(MIN(LN[Datum]);FILTER(LN;LN[Leistung...]="Objektbegehung"); everthing is clear to me.

Can u please forget the second filter (mandatID) i mentioned before and built the formula with the new expression earlier?

 

in hope to understand it and thank u a lot

Floriankx's avatar
Floriankx
Icon for Solution Sage rankSolution Sage
8 years ago

Hello,

 

FILTER(LN;LN[ID]=EARLIER(KontElement[ID])

Earlier works in row context. It takes the ID of the current row in LN and compares it to the IDs of KontElement and so filters all the IDs which are similar to the current ID (also order of the formular suggests upside down). The second filter eliminates all except Objektbegehung and the MIN Term gives you the earliest date.

  • BachFel's avatar
    BachFel
    Icon for Helper II rankHelper II
    8 years ago

     

     

    Probably I still didn´t get it..

    • Floriankx's avatar
      Floriankx
      Icon for Solution Sage rankSolution Sage
      8 years ago

      Hello,

       

      you wrote:

      FILTER(LN;KontElement[ID]=EARLIER(KontElement[ID])

      instead of

      FILTER(LN;LN[ID]=EARLIER(KontElement[ID])

       

      Please correct and let me know

      • BachFel's avatar
        BachFel
        Icon for Helper II rankHelper II
        8 years ago

        Hello,

         

        I wrote

         

        FILTER(LN;KontElement[ID]=EARLIER(KontElement[ID])

        instead of

        FILTER(LN;LN[ID]=EARLIER(KontElement[ID])

         

         

        because in the table LN there is now column with the expression you took.