9 Replies Latest reply on Jun 7, 2018 8:25 PM by Sheng Zhong

    LOD Fixed question

    Sheng Zhong

      Hi, Tableau Helper

        I have a question about LOD fixed. Here is a screen shot:

       

      Test1 is calculated field:

      However, Test another calculated field involved with LOD does not work as what I expect. Ideally it should sum all test1's values to 3 instead of 1 currently. I try to use window_sum, it does work but it prevent me from later calculation because I can not use window_sum within another window_sum like. So I have to apply LOD to this calculated field, but why does it so far yield 1 ? where is wrong on my formula?  I have attached twbx with this message. Thanks for your help!

        • 1. Re: LOD Fixed question
          Ivan Young

          It yields one because test 1 has either a value of 0 or 1.  If you fix test1 at the supplierID level it will still have a maximum value of 1.

          • 2. Re: LOD Fixed question
            Sheng Zhong

            Hi, Ivan

            Thanks for your reply and help. I changed my test1 to:

            and keep test unchanged. But it still does not yield the value I want. Ideally "test" should yield 3

            • 3. Re: LOD Fixed question
              Ivan Young

              Hi Sheng,

              Give this a try,

               

              1. Create a custom date for Period.  Select Months and Date value.

               

              2. Alter test1 to { FIXED [Supplier], [Period (Months)] : max(IIF(year([Period])=year([End Date])and (MONTH([End Date]))-[Comparison  Period]<=month([Period]) and MONTH([Period])<=MONTH([End Date]),1,0)) }

               

              3.  Alter test to {FIXED [Supplier]: SUM([Test1])}

               

               

              Let me know if it works for.

               

              Regards,
              Ivan

              • 4. Re: LOD Fixed question
                Deepak Rai

                Hi Sheng,

                You Need This?

                Thanks

                Deepak

                 

                • 5. Re: LOD Fixed question
                  Sheng Zhong

                  Hi, Ivan

                  Yes it works. Thanks for your help! But I just cannot think through logically. for "Test1", why I need to fix on yearly and monthly level in order to achieve that?

                  • 6. Re: LOD Fixed question
                    Sheng Zhong

                    Yes it works. Thanks for your help. But why I need to fix on yearly and monthly level in order to achieve that?

                    • 7. Re: LOD Fixed question
                      Deepak Rai

                      Your Test1 returns values like that and Test has to be an aggregation of Test1 per Company.

                      • 8. Re: LOD Fixed question
                        Ivan Young

                        Hi Sheng,

                        I'll do my best to explain although it's a little tough to articulate

                         

                        First I'm going to nest both expressions

                         

                        {FIXED [Supplier]: SUM({ FIXED [Supplier] : max(IIF(year([Period])=year([End Date])and (MONTH([End Date]))-[Comparison  Period]<=month([Period]) and MONTH([Period])<=MONTH([End Date]),1,0)) })}

                         

                        The inner expression representing test1 will be calculated first and the expression will return single value of 1 when fixed at the supplier level.

                         

                        The outer expression will then be evaluated which is {FIXED [Supplier]: SUM(1) }

                         

                        What you are trying to do in Test is count the number of months where the following is true which is why you need to add period months to your view.

                         

                        year([Period])=year([End Date])and (MONTH([End Date]))-[Comparison  Period]<=month([Period]) and MONTH([Period])<=MONTH([End Date].I c

                         

                        It does take a little while to wrap your head around how LODs work.  LODs work a bit like a group by in SQL which you join back to your primary source.  In fact I used SQL to replicate LOD functionality before 9.0.

                         

                        If you are a good with SQL I could try to explain it in those terms.

                         

                        Regards,

                        Ivan

                        • 9. Re: LOD Fixed question
                          Sheng Zhong

                          Thanks for your explanation, Ivan