BO Webi - Need Variable(s) to Remove Nulls for Crosstab Table

[jsisock79]

I’m looking to create a variable to remove unnecessary null values from my query results. I will also need that variable to create a crosstab to count the acuity levels by day. Some of the query results return a line with a numeric result and line with a null result. Others will just return one or the other. The caveat is that I need to keep the null results if they are the only result for that patient. I created a max acuity variable which does only display the maximum and keeps the null if that is the only value listed, but I can’t seem to use it correctly in the crosstab.

Please see sample data below and a sample cross tab. The cross tab is using the current acuity dimension which includes the additional null values I don’t want. The other fields on the cross tab are variables Tracking Date and # of Patients (see below).

[Tracking Date] =FormatDate([Start Tracking Date & Time];“MM/dd/yyyy”)

[# of Patients] =Count([Financial Number])

[MarkP]

The workaround for this situation in crosstabs is to create a dummy measure variable with the formula of =Sum(1).

Then, apply conditional formatting over the top and apply your Max Acuity Level variable where cell value >0 (i.e. always)

[nscheaffer]

I have working on this one for a while. I do not have this figured out, but perhaps someone here could add to what I have started. I will copy some of my StackOverflow answer here.

Here is the dbfiddle on which I based a free-hand SQL query.

My approach requires three variables. A different approach may be possible requiring less variables or the formulas in the variables could be consolidated. However, I like to keep them separated for better understanding of the logical progression and better maintainability.

Var Acuity Level Adjusted gets set to -1 if the Acuity Level is Null and otherwise leave it as is just to make it easier to deal with…

=If(IsNull([Acuity Level]); -1; [Acuity Level])

Var Max Acuity Level is the greatest value of Var Acuity Level Adjusted within each combination of Patient Name and Encounter Type. This is called a calculation context. I do not understand the nuances of this topic well enough to explain why what I have below works, but it does. I refer to that previous link a lot. Also, this is why it was important that I picked -1 to replace Null.

=Max([Var Acuity Level Adjusted]) In ([Patient Name]; [Encounter Type])

Var Max Filter flags the row where the first two variables are equal. This variable is necessary because you cannot filter based on one object relative to another object.

=If([Var Acuity Level Adjusted] = [Var Max Acuity Level]; 1; 0)

Now if I add those variables it looks like this…

Then we can add a filter to only show the records where Var Max Filter = 1. You can hide the extra columns or even delete them from the table.

Here is where things break down. When I try to build the crosstab I cannot seem to filter it down to just count the records with the maximum Acuity Level for each patient. Here is what I have color coded to correlate what I understand to be counted where.

Now if I color code the source data…

The two lines I have crossed out are still getting counted. In the crosstab the intersection of 09/20/2022 and Null should be 0 (technically Null) and the intersections of 09/21/2022 and Null should be 1 rather than 2. I do not have any filter applied to the crosstab; Whatever I have tried does not work. @MarkP can you explain your approach further based on what I have shown here so far? Or am I completely on the wrong track?

Noel