5 Replies Latest reply on Nov 12, 2018 7:12 AM by Tina Crouse

    Max Quarter Calculation with additional aggregation

    Tina Crouse

      I have a view where I show the max of a value during a quarterly period. Example:

       

      Jan 2018     Feb 2018     Mar 2018     Apr 2018     May 2018     June 2018

      10000           11000        9000              12000        15000          11000

       

      I have a calculated field that takes the max and in my view my column is set to quarter.

      max({INCLUDE [INCURREDYEAR (copy)]: sum([MEMBERCOUNT])})

       

      I end up with the view as:

       

      Q1               Q2

      11000     15000

       

      Now in my next view I want to take those two values and get the difference. I thought I could do a window_sum(([maxquarter]) - lookup([maxquarter]),-1) but that does not work. I just end up with 0 across the board.

       

      Any advice?

        • 1. Re: Max Quarter Calculation with additional aggregation
          Shinichiro Murakami

          Could you please attach mocked up sample data as twbx.

           

          Thanks,

          Shin

          • 2. Re: Max Quarter Calculation with additional aggregation
            Norbert Maijoor

            Hi Tina,

             

            Find my approach as reference below and stored in attached workbook version 2018.2 located in the original thread

             

             

            Assumption: Month is a date field

             

            1. Define Custom Date field D1. Month (Quarters)

             

             

            2. Define M1. Max Quarter Member count: {fixed [D1. Month (Quarters)]:max([Membercount])}

             

            3. Define M2. Delta Max Quarter Member count: ZN(SUM([M1. Max Quarter Member count])) - LOOKUP(ZN(SUM([M1. Max Quarter Member count])), -1)

             

            4. Drag the required objects to the indicated locations

             

             

            Hope it helps,

             

            Regards,

            Norbert

            • 3. Re: Max Quarter Calculation with additional aggregation
              Tina Crouse

              Thanks. It almost works. I added the state plan into the calculation because state plan is also in the information but the numbers do not match. I wished I could provide an example workbook but it would violate company policy since I work for a healthcare provider.

              • 4. Re: Max Quarter Calculation with additional aggregation
                Tina Crouse

                A better example would be filters are by mbulevel, productdesc, fundingtype, region, and sub region

                 

                Dimensions in the view: column is the date and I can do the custom date. There is also a fake header for 'Change' then the row shelf is the stateplan

                 

                It looks like from the prior view where I show q1 versus q2

                 

                stateplan     jan18     feb18     mar18     apr18     may18     jun18

                CA             6.1 mil     6.3mil     5.9mil     7.2mil     6.9mil     7.8mil

                CO             1.3mil     1.1mil     1.0mil     1.4mil     1.7mil     1.2mil

                DC              100          102        115        120         171        170

                 

                I process this way for the first view which shows q1 versus q2  max({INCLUDE [INCURREDYEAR (copy)]: sum([MEMBERCOUNT])})

                I get:

                stateplan     q1               q2

                CA               6.3mil     7.8mil

                CO               1.3mil     1.7mil

                DC               115          171

                 

                The second view is the variance between what I was able to show in the first view.

                I have a new sheet and pull in my date information which is my column and stateplan on my row shelf and then have two calculations.

                 

                {fixed [INCURREDYEAR (copy) (Quarters)], [STATEPLAN]

                :max([MEMBERCOUNT])}

                 

                ZN(SUM([MaxDefine])) - LOOKUP(ZN(SUM([MaxDefine])), -1)

                 

                The end calculations are not calculating correctly. I should end up with:

                 

                stateplan     change

                CA               +1.5mil    

                CO               +.4mil  

                DC               +56

                 

                I will keep Googling until I figure it out.

                • 5. Re: Max Quarter Calculation with additional aggregation
                  Tina Crouse

                  I got it to work by doing this:

                  View 1: 1st calculation is MaxQuarter: max({INCLUDE [INCURREDYEAR (copy)]: sum([MEMBERCOUNT])})

                  1st view has Q2 and Q3 for 2018 displayed

                   

                  View 2: 1st calculation is Diff: ZN(([MaxQuarter])) - LOOKUP(ZN(([MaxQuarter])), -1)

                  2nd view has change as the header and shows the different between Q1 and Q2. Included Diff as text and color so I can say where an increase of membership occurs that is black and decrease is red

                   

                  View 3: Rolling 5 quarters to show Q3 17, Q4 17, Q1 18, Q2 18, and Q3 18 displayed as a graph using the calculation for view 1

                  1 of 1 people found this helpful