1 2 Previous Next 15 Replies Latest reply on Mar 16, 2017 10:36 AM by PJ S

    LOD Expressions

    PJ S



      I have a viz like this, Week Day vs Sales and Goals (Dual axis, bar-sales, line goals)
      bars coloured by sub cat


      filters- city, year, month, sub cat parameter (since I need only few sub cats, used a parameter)


      when filtered by sub cat
      I need to have goals remain constant at category level while sales should be filtered accordingly

      and city, year, month filters should apply to both sales and goals.


      So I used a goal calc like this
      {FIXED [WeekDay],[Year Date],[Month Date],[City] : SUM([Goal])}
      but something is not right here and its not working.

      any inputs??? Thanks in advance

        • 1. Re: LOD Expressions
          Budi Lubis

          Are Year and Month date or integer?

          • 2. Re: LOD Expressions
            PJ S



            they are calc fields based of date fields

            datepart('year/month/weekday', [Date])

            • 3. Re: LOD Expressions
              PJ S

              I tried changing it to date format, I have same results either ways

              • 4. Re: LOD Expressions
                Budi Lubis

                Have you tried custom date instead?  for both year, month and day of week?

                1 of 1 people found this helpful
                • 5. Re: LOD Expressions
                  PJ S

                  I don't think I can do custom dates, since I need month, year as global filters.


                  But I tried a similar calculations for exact date vs Sales and Goals and it worked.

                  { FIXED  [Day-Date], [City], [Year Date],[Month Date] : SUM([Goal]) --> this one worked


                  and for week day viz

                  {FIXED [WeekDay],[Year Date],[City] : SUM([Goal])} --> this works, which wont be right if month filter is applied


                  the only prob is with this calc {FIXED [WeekDay],[Year Date],[Month Date],[City] : SUM([Goal])}

                  • 6. Re: LOD Expressions
                    Michael Hesser

                    Hi PJ;


                    Maybe you could tell us how the calculation is not working? What error are you receiving, or is the total incorrect? Is it possible to attach a TWBX?


                    My Thoughts:


                    #1 Are all your dimensions coming from the same data source? This can frequently cause problems.

                    #2 Are there other filtered dimensions besides these 4 that must be taken into account to form the Goal?


                    #3 If they are all coming from the same source (no blending), I'd suggest a little sleuthing to see if your data matches your expectations.


                    Start with a blank workbook and place [City],[Year Date],[Month Date],[WeekDay] on the Rows, with SUM([Goal]) on Text(value).

                    Eyeball the data and see if it looks OK (I realize that's not very specific, but if you see, for example, no values at all, you know something is amiss).


                    Move SUM([Goal]) onto Rows and change it to discrete, and rebuild a copy of your calc from scratch:


                    Start with:


                    Global Goal

                    {FIXED [City]:Sum([Goal)}


                    and drop this onto Text... make sure every City has a value.


                    Now edit the formula, dropping [Year Date] into it...


                    Global Goal

                    {FIXED [City], [Year Date]:Sum([Goal)}


                    And review before adding all fields. Hopefully this may allow you to determine where your problem lies.



                    1 of 1 people found this helpful
                    • 7. Re: LOD Expressions
                      PJ S

                      Hi Michael,


                      Thanks for the inputs, I tried adding one by one to fixed and that's how I figured out that Month is the only thing that's not working and yes everything is from same data source, the only reason I want Goal to remain at category level when sub cat is filtered is because we don't have Goals for sub cat. So the user want goals to remain at cat level while sales is filtered by sub cat.


                      When I filter sub cat, tableau is trying to calculate goals for each sub cat, so I ve tried LOD, above pattern calc worked for 5/6 sheets and I am not really sure what the issue is here and I am sorry cannot attach workbook. However I gave all the necessary inputs.

                      Working for the same on new workbook is not changing the results.



                      • 8. Re: LOD Expressions
                        Michael Hesser

                        Interesting. [Year Date], [Month Date] and [WeekDay] should all be components of [Date], so if one works, they should all-- unless there is something amiss with your formula.


                        So what happens when you remove the equation reference, substituting it instead with DATEPART('month', [Date]) ?

                        1 of 1 people found this helpful
                        • 9. Re: LOD Expressions
                          PJ S

                          DATEPART('month', [Date]) is not working, I am using 9.1. Its asking to give field names only, so created a field with the same to use in LOD expressions.

                          While playing with it, I ve been playing with only one filter, so it looked like month is the issue but the whole expression is the problem here. Month is not working either.


                          Not really sure how only this doesn't work

                          {FIXED [WeekDay],[Year Date],[Month Date],[City] : SUM([Goal])}



                          while these work

                          {FIXED [Exact Date], [Year Date],[Month Date],[City] : SUM([Goal])}

                          {FIXED [Year Date],[Month Date],[City] : SUM([Goal])}

                          • 10. Re: LOD Expressions
                            Michael Hesser

                            I wonder if your parameter is the issue.


                            Is [WeekDay] in the [problem] visualization?

                            If it's not included in the view, you may not get results (Tableau won't know what value to present).


                            Try adding [WeekDay] the viz and see if the calculation works.


                            Note: If your parameter is removing [Weekday] from the viz, you can change the calculation, as well:


                            IF [View Parameter] = "State1" then

                            {FIXED [WeekDay],[Year Date],[Month Date],[City] : SUM([Goal])}


                            {FIXED [Year Date],[Month Date],[City] : SUM([Goal])}



                            Good luck!

                            1 of 1 people found this helpful
                            • 11. Re: LOD Expressions
                              Bruno Musolino

                              Have you tried using:


                              {EXCLUDE [Sub-category]: SUM([Goal]} instead of the FIXED LoD?

                              According to you description, since goals should remais at category-level, this seems more appropriate.


                              Let me know if this doesn't work.

                              1 of 1 people found this helpful
                              • 12. Re: LOD Expressions
                                PJ S


                                Tried Exclude but it doesn't work on all scenarios.

                                However FIXED worked fine if I re-started a sheet and put sub cat on color as a last step.

                                But it doesn't work at city-system level, meaning if a single city is selected my goals work to my expectation, but if I select (All) on city's quick filter, goals are not working as expected



                                • 13. Re: LOD Expressions
                                  Bruno Musolino

                                  Can you provide a twbx workbook with fake data so we can go deeper on what is happening?

                                  Also, what scenarios the EXCLUDE didn't work? You mean in the same sheet with different filter scenarios applied or other sheets/calculations?


                                  Instead of using the calculated fields for weekday and year, have you tried using the date field on the view and having it set to weekday and year where appropriate while having the date field itself on the FIXED LoD?

                                  1 of 1 people found this helpful
                                  • 14. Re: LOD Expressions
                                    PJ S

                                    I needed separate fields for year and month so Year([Date]) or datepart('year', [Date]) did not work, it resulted error 'only filed names permitted'. I do not exactly remember where exclude did not work since I played a lot with LODs.

                                    1 2 Previous Next