7 Replies Latest reply on Nov 9, 2016 12:43 PM by Doug Carter

    Custom Quarters (rolling 6 months)

    Matthew Risley

      I have a very strange request that I can't seem to solve. Attached is a workbook with non-confidential dummy data.

       

      I have data from October 2015-October 2016. The fiscal year starts in July.

       

      I have data here about what "Type of Guy" people are (i tried to make it fun).

       

      But Company ABC has two divisions. One is normal. But one division wants to have custom quarters that roll every six months. Essentially combining two quarters into one. This means that a quarter using "normal" quarter logic is a part of two separate quarters. I try to show their needs using the arrows below.

       

       

      So the correct values for # of Bad guys should be:

                          # Bad guys        

      Q2 FY 17|          4872            |

      Q1 FY 17|          9613            |

      Q4 FY 16|          12234          |

      Q3 FY 16|          11356          |

       

      I've tried a lot of things but can't seem to figure out a solution.

       

      PS: Moving Calculation seems like the answer. But I have a aggregate measures like %Good guys - %Not Too Good or Bad Guys that won't result in the correct information.

        • 1. Re: Custom Quarters (rolling 6 months)
          Doug Carter

          Hi Matthew!

           

          I am looking into this, but I needed to know, are the quarters for the second calculation set up in a different way? Like if we broke that Quarters down into months, which months would be part of the new quarters?

          • 2. Re: Custom Quarters (rolling 6 months)
            Matthew Risley

            They would be the months of the fiscal year as well.

            so Q2 FY 16 (red) is the months of  July, August, September, October, November, December (but really only July, August, September, October Because i only have up to October 2016)

             

            Is that what you are asking?

             

            and thanks for looking into this, Doug!

            • 3. Re: Custom Quarters (rolling 6 months)
              Doug Carter

              And here you go! Thank the Tableau Hivebrain for this, as I MOSTLY had it.

               

              The answer (in case you aren't running 10 and can't open the workbook) is to use the following formula:

               

              LOOKUP(SUM([Bad Guy]), 0)+LOOKUP(SUM([Bad Guy]), -1)

               

              This looks up the sum o' bad guys in the row you are in and then adds the row before.

               

              Hope this Helps!

              1 of 1 people found this helpful
              • 4. Re: Custom Quarters (rolling 6 months)
                Matthew Risley

                Doug,

                 

                This is great! This is sort of what I eluded to in the first post about a Moving Calculation.

                 

                So I have some fields like %Bad Guy, %Not So Good or bad. and then another calculation of (%Bad - %Not So..). Because these are Percentages, the Moving Calculation does not work for these. Any ideas on how to implement them using something similar to your answer?

                 

                PS: Company ABC does not pay for Tableau 10

                • 5. Re: Custom Quarters (rolling 6 months)
                  Doug Carter

                  Hi Matthew,

                   

                  Without other versions of tableau to work with, I can't post new workbooks (at TCC helping out customers!), BUT I think that if you manually build the %bad guy calculations in the previous calc it'll work. So it'd look like:

                   

                  (LOOKUP(SUM([Bad Guy]), 0)+LOOKUP(SUM([Bad Guy]), -1)/Lookup(sum(all guys), 0)+Lookup(sum(all guys)-1))*100

                   

                  Again this is strictly a hypothetical guess without working on a workbook. Lemme know if this works!

                  • 6. Re: Custom Quarters (rolling 6 months)
                    Matthew Risley

                    Didn't quite work. But I do appreciate the help!

                     

                    and have fun at TCC!

                    • 7. Re: Custom Quarters (rolling 6 months)
                      Doug Carter

                      Hi Matthew!

                       

                      See I had more time, this is what I created (and I created an arbitrary % of bad guys calc):

                       

                      Lookup(SUM([Bad Guy])/(SUM((zn([Bad Guy]))+SUM(ZN([Dunno]))+sum(ZN([Good Guy]))+SUM(ZN([Not Really Good or Bad]))),0)

                      +

                      LOOKUP(SUM([Bad Guy])/(SUM((zn([Bad Guy]))+SUM(ZN([Dunno]))+sum(ZN([Good Guy]))+SUM(ZN([Not Really Good or Bad]))), -1)

                       

                      % of bad guys is:

                      SUM([Bad Guy])/(SUM((zn([Bad Guy]))+SUM(ZN([Dunno]))+sum(ZN([Good Guy]))+SUM(ZN([Not Really Good or Bad])))

                       

                      The ZN is because Dunno only has 3 values and otherwise would not be useful for the calculation.

                       

                       

                      Hope this helps you!

                       

                      Edit to add bad guys to the calc, as SOMEONE here forgot to add them into the % of the whole.

                      1 of 1 people found this helpful