1 2 Previous Next 26 Replies Latest reply on Apr 9, 2017 1:06 AM by Segun Aiyegbusi

    LEVEL OF DETAIL

    Segun Aiyegbusi

      hi . I need to incorporate table calculation and if statement for countd in an LOD.

      COUNTD( IF {FIXED [Month]: SUM([Actual Spend])})/TOTAL(SUM([Actual Spend])) > ([%Spend]) THEN [Month] END).

      Any help would be appreciated.

        • 1. Re: LEVEL OF DETAIL
          Chris McClellan

          What version of Tableau are you using ?

          What error are you getting ?

          Can you supply some dummy data and an explanation of what you're trying to do ?

          1 of 1 people found this helpful
          • 2. Re: LEVEL OF DETAIL
            Segun Aiyegbusi

            Many thanks, chris. I am using the latest version of Tableau - 10.0.8.

             

            The reason why I have incorporated table calculation and if statement for a LOD Countd is because of an error details -  "Level of details expression cannot contain table calculations or the attr function".

             

            What I am trying to achieve from the below screenshot to count the numbers of month that have exceeded  Avg.%spend.

             

            In achieving this, I have in the in the 5th column a table calculation that has been derived using this calculation: SUM([Actual Spend])/TOTAL(SUM([Actual Spend]))

             

            I now want to further calculate the count of the month that have exceeded Avg.%Spend. However, using  - IF [1 SUM([Actual Spend]])/TOTAL(SUM([Actual Spend]]))] > AVG([%Spend]) THEN COUNTD([Month]) end - which is giving a grand total of 12.

             

            Reading from the solution in the community the other possible way is to use an LOD expression using - COUNTD( IF  { FIXED [Month] : ([1 SUM([Actual Spend]])/TOTAL(SUM([Actual Spend]]))])} > ([%Spend]) THEN [Month] END) - but  I am getting "Level of details expression cannot contain table calculations or the attr function"

             

            Please see the attached packaged workbook.

             

             

            1 of 1 people found this helpful
            • 3. Re: LEVEL OF DETAIL
              Chris McClellan

              Segun Aiyegbusi wrote:

               

              Many thanks, chris. I am using the latest version of Tableau - 10.0.8.

               

               

              Latest version is now 10.2, but don't worry Tableau releases new versions very frequently

               

              Segun Aiyegbusi wrote:

               

               

              What I am trying to achieve from the below screenshot to count the numbers of month that have exceeded  Avg.%spend.

               

              Please see the attached packaged workbook.

               

               

               

              Try the attached, check out Sheet 2 & 3 and Calculation 1 and 2

              2 of 2 people found this helpful
              • 4. Re: LEVEL OF DETAIL
                Segun Aiyegbusi

                Many thanks, Chris... All done!

                1 of 1 people found this helpful
                • 5. Re: LEVEL OF DETAIL
                  Chris McClellan

                  Maybe you should mark my answer as correct rather than your own

                  1 of 1 people found this helpful
                  • 6. Re: LEVEL OF DETAIL
                    Segun Aiyegbusi

                    Sorry, mate. Wasn't looking

                    1 of 1 people found this helpful
                    • 7. Re: LEVEL OF DETAIL
                      Segun Aiyegbusi

                      Hi Chris,

                       

                      I was also hoping to apply the same LOD principle to a much larger database. However, I think it's much complex as I have more filters in the mix. I have tried also adding other dimensions into  Fixed LOD to no avail. Would it be possible that I am missing something.

                      • 8. Re: LEVEL OF DETAIL
                        Chris McClellan

                        I can't see what's wrong with that, what a re you aiming for ?

                        • 9. Re: LEVEL OF DETAIL
                          Segun Aiyegbusi

                          Thanks, Chris for your response. I am aiming apply the same LOD calculation in the last Twbx extract I sent but getting a  count 9 instead of a count of 3.

                           

                          Given that the first Twbx I sent  is a small extract of the second Twbx  with a lot more filters in the mix of which i think the LOD calculation is just factoring the month.

                           

                          Many thanks for your help.

                          • 10. Re: LEVEL OF DETAIL
                            Okechukwu Ossai

                            Hi Segun,

                             

                            Chris may come up with a different solution but I've got something that works. By adding those filters, the LOD calculation was just factoring the month out of context and ignoring the correct partitioning of your data.

                             

                            To make this to work you need to specify the dimensions you want to be considered in calculating both % Spend and % Continuity.

                             

                            Replace [Calculation1] with this code;

                             

                            IF {FIXED [Type],[L4 PCAT Category Desc],[Year],[Country],[Brand Pos Name],[Principle],[Campaign Name],[Month] :

                                SUM([Actual Spend])} / {FIXED [Type],[L4 PCAT Category Desc],[Year],[Country],[Brand Pos Name],[Principle],[Campaign Name]: SUM([Actual Spend])}

                            > {FIXED [Type],[L4 PCAT Category Desc],[Year],[Country],[Brand Pos Name],[Principle],[Campaign Name],[Month] : AVG([%Spend])} THEN 1 ELSE 0 END

                             

                            See attached workbook. Hope this helps.

                             

                            Ossai

                            2 of 2 people found this helpful
                            • 11. Re: LEVEL OF DETAIL
                              Segun Aiyegbusi

                              Thanks Okechuckwu, this has really helped.

                              Are you based in London?

                              • 12. Re: LEVEL OF DETAIL
                                Okechukwu Ossai

                                You're welcome Segun. Glad it helped.

                                 

                                Yes, I am based in London.

                                 

                                Ossai

                                • 13. Re: LEVEL OF DETAIL
                                  Segun Aiyegbusi

                                  Apologies Okechukwu - I am just getting to grips with using this platform. 

                                  • 14. Re: LEVEL OF DETAIL
                                    Okechukwu Ossai

                                    No worries, I understand. Added you on Linkendln.

                                    1 2 Previous Next