Chart Trouble

[#1]

Good Morning,

I'm working on this project and I'm not sure how to set up the functionality.

What I'm trying to set up is a stacked bar and line chart combo. For my dimension I want it to Month and Day. The stacked bars will represent paid and denied claims whereas the line will represent (paid+denied)/(paid+denied+incomplete) claims.

The first trouble I've run into is setting up the dimension because I'd like to add a special functionality to it. I'd like for the user to choose a [Date of Service] (split into month and day) which will update the lower end of the x-axis to that date. I want this to affect the expression so that it only shows claims that have that date of service as well. I'd like the upper end of the x-axis to use =today(2). I'm not sure how to activate the x-axis in order to do this.

The second trouble I've run into is the expression. Counting the status of whether they're paid or denied isn't the problem. I'd like to have their [Date of First Check] be the determining factor. It's kind of hard to explains I'll try an example:

User selects Date of Service (Month = August & Day = 2)

All claims with the date of service with August 2nd will be selected.

On the chart I should see those claims falling into place on the dimension by the dates they were paid or denied (calling it [Date First Check] for now til I get the kinks worked out). On each of those dates the stacked bars will show the percentage between those two - overall.

In other words, these claims that were selected for August 2. For these claims, lets say that on August 5th, 5% are paid and 3% are denied. Then say for August 10th, we see that 30% are paid and 6% are denied (these including the 5%/3% from before).

Then the line part of this chart will be a representation of (paid+denied)/(paid+denied+incomplete) overall.

I know all of this is a mouthful. Can anyone point me in the right direction to getting this solved?

Thanks!

[#2]

What do you have so far? That is a lot, but I didn't see anything that would be impossible. The user is going to select a Month of Service and a Day of Service (names I will use in my example, change to whatever you are using). You should be able to handle the Date of Service and the Date of First Check axis using the selection and Set Analysis. What are the expressions for Paid & Denied?

Something like:

Count({<ServiceDate={'$(=MakeDate(Year(Today()), MonthofService,DayofService))'},
 DateofFirstCheck={'>=$(=MakeDate(Year(Today()),MonthofService,DayofService))
 <=Today(2)'}>}
Paid)

If DateofFirstCheck is the dimension, then this will only count those records with a Service Date on the selection and a Date of First Check between the selected date and Today(2). You could also use a calculated dimension to handle the Date of First Check range, but I prefer to handle that stuff in the expression.

I'm guessing that you will also want some accumulation on the expression, since you want the previous dates to be included.

There is a lot of info in your message and we don't know a lot about your data structure, which makes it difficult to provide precise answers. Maybe if you could give some more info or break down your requirements into more managable pieces, it would help. Some info on the expressions you are currently using for some of this info would certainly be helpful.

[#3]

Your suggestion was actually the direction I was gravitating towards.

I have two list boxes at the moment, one with the Month([Date of Service]) and another with Day([Date of Service]) for the user to select.

I did decide that the dimension should be the [Date First Check]... which I should change over to [Date First Response] but I'll work on that later, for now I'm using [Date First Check].

The expressions are still giving me trouble though. In this prototype of a data set, I put three flags, Accepted, Denied, or Incomplete (Incomplete meaning no response yet).

When I do a =frequency() in excel, i get these numbers:

Accepted	6675
Denied	3258
Incomplete	66

I think i finally have the counts of these expressions reflecting those numbers accurately, but I cannot get them to stack into percentages properly.

Let's we're looking at only 10 claims say on Day 1 I have 3 claims paid and 1 denied, I want the stack between paid and denied to equal 40%. On day 2 lets say 2 more claims were paid and one more was denied (so 5 paid, 2 denied), I should see the paid % = 50 and denied % = 20, with the overall stack = 70%.

The behavior I'm seeing now is at the end, lets say only 3 claims were denied out of the 10. The first one will display as 33%, the 2nd at 66%, and the last at 100%. So in essence, these stacked bar charts are showing 200% (100% for each paid and denied).

I haven't given any data at this point because the data itself is double pivoted. So the set analysis I'm having to use on this chart is something along the lines of this:

count({$<[Variable]={'Paid'},[ICDVariable Pay]={'ICD1'},[ClaimDenial Pay]={'Accepted'}>} [ClaimDenial Pay])

Where [Variable] and [ICDVariable Pay] were created in a pivot in 2 separate cross tables.

I'll try refining my x-axis with your suggestion and see where that takes me.