4 Replies Latest reply on Sep 2, 2016 9:30 AM by joe major

    Help with differentiating Quarters

    joe major

      Hi,

      I'm having difficulty showing the proper actuals for each Q1.  The issue I think is that each has a different "MONTH" when reported.  When I filter out one or the other MONTHS, the Quarter associated with that MONTH turns off.  I think I need some sort of calculated field that says:  If MONTH = FY 17 AUG AND SHIPMENT MONTH equals the shipment months associated with that "MONTH" then show the % .

      Attached is a dummy workbook. Any help is appreciated.

       

       

      q1.JPG

        • 1. Re: Help with differentiating Quarters
          chris.moore.11

          Hi, hopefully i'll do a good job explaining this.

           

          Your Q1 FY2017 calculation has "shipment months" that aren't in the grouping for them but are used in your calculation.

           

          Capture.PNG

           

          There are two quick ways to deal with this. You can create a calculated field for a filter that has logic for shipment months and quarters. Like this...

           

          Or you could create two seperate measures for each quarter that has that filter logic built into them. (This way would be more flexible actually because you dont have to apply those filters)

           

          Attached has that calculation for a new filter.

           

          Hopefully this helps.

          1 of 1 people found this helpful
          • 2. Re: Help with differentiating Quarters
            joe major

            That's awesome.  thank you.  I think I did miss something that you brought up.  I have to create a year over year calc after the correct numbers show up.  I think the only way to do that is to create two measures so that I can do a Q1 16 - Q1 '17. Can you help with that?  Thank you.

            • 3. Re: Help with differentiating Quarters
              chris.moore.11

              Yeah, I'll try to step you through it.

               

              Basically you take the logic from the filter and use it inside a calculated measure(s). So we will create a [TS] and [SN] for each quarter of interest based on the quarter. Also, we can take off filters for month or quarter because we are essentially filtering within our new measure.

               

              So we would make a [TS - Q1 FY2016] that uses logic to only return then [TS] value if it the correct month and quarter, and otherwise return null. It would be:

              if

              ([Shipment Month] = "AUG FY2016" or [Shipment Month] = "JUL FY2015" or [Shipment Month] = "JUN FY2015" or [Shipment Month] = "MAY FY2015") and [Quarter] = "Q1 FY2016" then [TS]

              else null end

               

              Then we would do the same thing for the [NS - Q1 FY2016]....

              if

              ([Shipment Month] = "AUG FY2016" or [Shipment Month] = "JUL FY2015" or [Shipment Month] = "JUN FY2015" or [Shipment Month] = "MAY FY2015") and [Quarter] = "Q1 FY2016" then [NS]

              else null end

               

              Then you would create your AR% speciccifin for the quarter using those two. So the [AR% - Q1 FY2016] would be just like your original but use our new fields...

              SUM([NS - Q1 FY2016 ])/sum([TS - Q1 FY2016])

               

              Then you would repeat that with the appropriate logic for as many quarter as you would like. Hopefully this helps, see the attached workbook for more help.

              • 4. Re: Help with differentiating Quarters
                joe major

                This looks great.  I'll work on implementing it now.  Thanks!!!