Report with some strange twists

building reports desktopintelligence
1

I am working on a report that the business unit has added some twists and turns for me to find my way through. Report will be bringing in 60 months worth of data (long running queries) with multiple breaks by place of service and services throughout.

example of output I have multiple columns doing similar things I am only showing one on this example.

(Month end) (claims/members) (annual % change) (Quarterly % Change)
01/31/2000 $200 0 0
02/29/2000 $200 0 0
03/31/2000 $100 0 0
04/30/2000 $500 0 0
05/31/2000 $100 0 0
06/30/2000 $300 0 160% current/prior qtr

this would continue month by month until 60 months are displayed. It will not always be starting in January. Quarter logic will always be based on calander quarters.

01/31/2001 $100 (50%)

Once months are displayed so there are months a year apart in this example (claims paid Jan 2001) / (claims paid Jan 2000) annual change will start displaying

Issues I am working through on report:
How can I identify prior Qtrs I can force context and get a dollar sum by quarter but am stumped at how to find previous for quarter change variable.

Same with year how to identify dollars from Jan 2001 to be divided by Jan 2001

Currently I am working with 2 queries. One bringing in dollars for claims the other member counts. I am trying to limit queries due to the level of granularity and very long run times.

Mike Murray EDS
Wellmark Blue Cross Blue Shield of Iowa
mike.murray@eds.com
change is inevitable, progress however is optional
:confused:

mmurray (BOB member since 2003-01-13)

2

Huh? :confused: Can you restate that question? I don’t understand.

You’re trying to compare one month to itself?

You may need to include some output contexts in your formulas:

Sum() Where ( = 2001) - Sum() Where ( = 2000)

You get the idea?

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

3

Sum() Where ( = 2001) - Sum() Where ( = 2000)

I am sort of following. What I need to do is take the current month year measure and divide it by the prior month year measure. I do not want to hard code years into variables since they will change.

I tried by building a variable for prior year using current year - 1 which works. Then following your suggestion
Sum() Where ( = ) I get syntax errors when trying to build a variable for prior year measure.
It will work if I set the value for year like
Sum() Where ( = 2001)

Mike Murray EDS
Wellmark Blue Cross Blue Shield of Iowa
mike.murray@eds.com
change is inevitable, progress however is optional

mmurray (BOB member since 2003-01-13)

4

Mike,

In the “Where” statement of your calculation, you cannot use variables on the right side of the “=” sign. You must use hard coded values. Try creating a couple Flag variables such as the following:

Flag1: If = Year(CurrentDate()) Then 1 Else 0
Flag2: If = Year(CurrentDate()) - 1 Then 1 Else 0

Then use the following for your formula:

Sum() Where ( = 1) - Sum() Where ( = 1)

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

5

what I did to get it to work was add a lengthy if statement.
if year = 1999
then measure where year = 1999 / measure where year = 1998
else if year = 2000…
the report will now last until 2010

thanks for the help!

Mike Murray EDS
Wellmark Blue Croo Blue Shield of Iowa

mmurray (BOB member since 2003-01-13)