10 Replies Latest reply on Aug 14, 2019 3:51 AM by Jim Dehner

    LOD Calc, Ifs, ands, ors, ATTRs.

    Abby Murphy

      I know I've made this more complicated than necessary.  It all worked except I forgot the part of the LOD. I seem to be having issues adding the LOD now.  I'm getting errors about aggregates and attributions.  Any advice on where I put the LOD part into this calculation??

       

      IF (ATTR([Channel Name]) = 'Drywall distribution'

      OR ATTR([Channel Name]) = 'Hardware (incl LBM)'

      OR ATTR([Channel Name]) = 'Industrial/STAFDA'

      OR ATTR([Channel Name]) = 'paint stores')

      AND

      ATTR([Type Id]) != 60

      AND

      (

      (ATTR([Shipped To State]) = 'Maine'

      OR ATTR([Shipped To State]) = 'New Hampshire'

      OR ATTR([Shipped To State]) = 'Connecticut'

      OR ATTR([Shipped To State]) = 'Rhode Islande'

      OR ATTR([Shipped To State]) = 'Vermont'

      OR ATTR([Shipped To State]) = 'Massachusetts')

      OR

      (ATTR([Shipped To State]) = 'New York'

      AND ATTR([Ship To City]) = 'Buffalo')

      OR

      (ATTR([Shipped To State]) = 'New York'

      AND ATTR([Ship To City]) = 'Syracuse')

      OR

      (ATTR([Shipped To State]) = 'New York'

      AND ATTR([Ship To City]) = 'Albany'))

      THEN SUM([Sales])*[Commission %]

      ELSE 0

      END

        • 1. Re: LOD Calc, Ifs, ands, ors, ATTRs.
          Zhouyi Zhang

          Hi, Abby

           

          Is there any sample workbook to share? and why you need lod in this expression?

           

          ZZ

          • 2. Re: LOD Calc, Ifs, ands, ors, ATTRs.
            Anuj yadav

            Your LOD would look something like this

             

            { Fixed [Dimension]:

            Sum(IF (ATTR([Channel Name]) = 'Drywall distribution'

            OR ATTR([Channel Name]) = 'Hardware (incl LBM)'

            OR ATTR([Channel Name]) = 'Industrial/STAFDA'

            OR ATTR([Channel Name]) = 'paint stores')

            AND

            ATTR([Type Id]) != 60

            AND

            (

            (ATTR([Shipped To State]) = 'Maine'

            OR ATTR([Shipped To State]) = 'New Hampshire'

            OR ATTR([Shipped To State]) = 'Connecticut'

            OR ATTR([Shipped To State]) = 'Rhode Islande'

            OR ATTR([Shipped To State]) = 'Vermont'

            OR ATTR([Shipped To State]) = 'Massachusetts')

            OR

            (ATTR([Shipped To State]) = 'New York'

            AND ATTR([Ship To City]) = 'Buffalo')

            OR

            (ATTR([Shipped To State]) = 'New York'

            AND ATTR([Ship To City]) = 'Syracuse')

            OR

            (ATTR([Shipped To State]) = 'New York'

            AND ATTR([Ship To City]) = 'Albany'))

            THEN [Sales]*[Commission %]

            ELSE 0

            END)}

            • 3. Re: LOD Calc, Ifs, ands, ors, ATTRs.
              Jim Dehner

              Hi Abby

               

              the lod will be based on some dimension - not mentioned in your post -

               

              you can wrap the above expression int the LOD  like    {fixed : sum( your expression  ) }    or you can include a dimension before the colon 

               

               

               

              BUT you big issue is that you can not use ATTR() in an LOD   -

              you will have to replace ATTR with wither MIN() or MAX()   in all cases - what you decide to use will depend on the data itself and the analysis -

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: LOD Calc, Ifs, ands, ors, ATTRs.
                Anuj yadav

                Oh right! I missed attr(). I this case I don’t think you would need to do a min or max. These are dimensions you are filtering, unless they are coming from a blended data. If it is blended data then I doubt it would work with LOD.

                 

                also, I noticed m, in my previous response I have sales * commission%.

                Unless you want commission for each sale, you should multiple commision% after the LOD is applied

                • 5. Re: LOD Calc, Ifs, ands, ors, ATTRs.
                  Abby Murphy

                  My LOD is a IDNumber.  I have already tried "wrapping the expression", but received the errors due to the ATTR I used.  When I put [Shipped To State] = 'New York' without the ATTR then I get errors about aggregate functions. 

                   

                   

                  • 6. Re: LOD Calc, Ifs, ands, ors, ATTRs.
                    Jim Dehner

                    I understand

                    you either have to use Min() or Max() around the shipped state - and yes you can use min() or max() around a dimension - Tableau uses alphameric sequencing and sorting - or there is one additional option

                     

                    i will get you started but you will have to take all the ATTR()'s yourself

                     

                    [fixed (IDnumber0):  sum(

                     

                    IF (([Channel Name]) = 'Drywall distribution'

                    OR ([Channel Name]) = 'Hardware (incl LBM)'

                    OR ATTR([Channel Name]) = 'Industrial/STAFDA'

                    OR ATTR([Channel Name]) = 'paint stores')

                    AND

                    ATTR([Type Id]) != 60

                    AND

                    (

                    (ATTR([Shipped To State]) = 'Maine'

                    OR ATTR([Shipped To State]) = 'New Hampshire'

                    OR ATTR([Shipped To State]) = 'Connecticut'

                    OR ATTR([Shipped To State]) = 'Rhode Islande'

                    OR ATTR([Shipped To State]) = 'Vermont'

                    OR ATTR([Shipped To State]) = 'Massachusetts')

                    OR

                    (ATTR([Shipped To State]) = 'New York'

                    AND ATTR([Ship To City]) = 'Buffalo')

                    OR

                    (ATTR([Shipped To State]) = 'New York'

                    AND ATTR([Ship To City]) = 'Syracuse')

                    OR

                    (ATTR([Shipped To State]) = 'New York'

                    AND ATTR([Ship To City]) = 'Albany'))

                     

                     

                     

                    THEN      (you remove this SUM   )       ([Sales])*[Commission %]

                    ELSE 0

                    END  })

                     

                     

                    you make the conditional statement at the no-aggregated level - take out all the attr() 's and then aggregate with sum outside the conditional

                     

                    Jim

                     

                    If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                    1 of 1 people found this helpful
                    • 7. Re: LOD Calc, Ifs, ands, ors, ATTRs.
                      Abby Murphy

                      I feel like I am SO Close.  I keep getting an error message of "Close parentheses expected at character 36", i've tried to add a parentheses everywhere and can't find the problem. 

                       

                      { FIXED [So Item Id]:

                      sum(

                       

                       

                      IF ((MIN([Channel Name]) = 'Drywall distribution'

                      OR MIN([Channel Name]) = 'Hardware (incl LBM)'

                      OR MIN([Channel Name]) = 'Industrial/STAFDA'

                      OR MIN([Channel Name]) = 'paint stores')

                      AND

                      MIN([Type Id]) != 60

                      AND

                      (

                      ((MIN([Shipped To State]) = 'Maine'

                      OR MIN([Shipped To State]) = 'New Hampshire'

                      OR MIN([Shipped To State]) = 'Connecticut'

                      OR MIN([Shipped To State]) = 'Rhode Islande'

                      OR MIN([Shipped To State]) = 'Vermont'

                      OR MIN([Shipped To State]) = 'Massachusetts')

                      OR

                      (MIN([Shipped To State]) = 'New York'

                      AND MIN([Ship To City]) = 'Buffalo')

                      OR

                      (MIN([Shipped To State]) = 'New York'

                      AND MIN([Ship To City]) = 'Syracuse')

                      OR

                      (MIN([Shipped To State]) = 'New York'

                      AND MIN([Ship To City]) = 'Albany')))

                      THEN ([Sales])*[Jackson Sales Commission %]

                      ELSE 0

                      END))}

                      • 8. Re: LOD Calc, Ifs, ands, ors, ATTRs.
                        Abby Murphy

                        This is what ended up working for me.  I had to remove the first SUM and add it to the bottom "Sales"

                         

                        { FIXED [So Item Id]:

                        (

                         

                         

                        IF (

                        (MIN([Channel Name]) = 'Drywall distribution'

                        OR MIN([Channel Name]) = 'Hardware (incl LBM)'

                        OR MIN([Channel Name]) = 'Industrial/STAFDA'

                        OR MIN([Channel Name]) = 'paint stores')

                        AND

                        MIN([Type Id]) != 60

                        AND

                         

                         

                        (

                        (MIN([Shipped To State]) = 'Maine'

                        OR MIN([Shipped To State]) = 'New Hampshire'

                        OR MIN([Shipped To State]) = 'Connecticut'

                        OR MIN([Shipped To State]) = 'Rhode Islande'

                        OR MIN([Shipped To State]) = 'Vermont'

                        OR MIN([Shipped To State]) = 'Massachusetts')

                        OR

                        (MIN([Shipped To State]) = 'New York'

                        AND MIN([Ship To City]) = 'Buffalo')

                        OR

                        (MIN([Shipped To State]) = 'New York'

                        AND MIN([Ship To City]) = 'Syracuse')

                        OR

                        (MIN([Shipped To State]) = 'New York'

                        AND MIN([Ship To City]) = 'Albany')

                        ))

                        THEN SUM([Sales])*[Jackson Sales Commission %]

                        ELSE 0

                        END)}

                        • 9. Re: LOD Calc, Ifs, ands, ors, ATTRs.
                          Anuj yadav

                          { FIXED :

                           

                          sum(

                           

                           

                           

                           

                           

                          IF ((MIN() = 'Drywall distribution'

                           

                          OR MIN() = 'Hardware (incl LBM)'

                           

                          OR MIN() = 'Industrial/STAFDA'

                           

                          OR MIN() = 'paint stores')

                           

                          AND

                           

                          MIN() != 60

                           

                          AND

                           

                          (

                           

                          ((MIN() = 'Maine'

                           

                          OR MIN() = 'New Hampshire'

                           

                          OR MIN() = 'Connecticut'

                           

                          OR MIN() = 'Rhode Islande'

                           

                          OR MIN() = 'Vermont'

                           

                          OR MIN() = 'Massachusetts')

                           

                          OR

                           

                          (MIN() = 'New York'

                           

                          AND MIN() = 'Buffalo')

                           

                          OR

                           

                          (MIN() = 'New York'

                           

                          AND MIN() = 'Syracuse')

                           

                          OR

                           

                          (MIN() = 'New York'

                           

                          AND MIN() = 'Albany'))))

                           

                          THEN ()*

                           

                          ELSE 0

                           

                          END)}

                          • 10. Re: LOD Calc, Ifs, ands, ors, ATTRs.
                            Jim Dehner

                            glad to have helped out on this

                            Jim

                            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.