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

# Max Quarter Calculation with additional aggregation

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.

• ###### 1. Re: Max Quarter Calculation with additional aggregation

Could you please attach mocked up sample data as twbx.

Thanks,

Shin

• ###### 2. Re: Max Quarter Calculation with additional aggregation

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

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

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

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