10 Replies Latest reply on Aug 27, 2018 10:08 AM by kushal acharya

# Calculating row level data based on multiple business logic

Hi everyone,

I am looking for ways to count cases which meet the business logic.

I have added the business logic to the attached tableau workbook (Version 2018.1). I have created the expected results in excel added to the workbook as well.

I am looking for some pointers please as I am badly stuck.

Thank you,

KA

• ###### 1. Re: Calculating row level data based on multiple business logic

If I understand correctly, in your LOD calc for Min Results you need to replace [Specimen Collection Date/Time] with your min collection date calc [Min (First) Specimen collection date] then remove the [Specimen Collection Date/Time] pill from rows and replace the [Results] pill with [Min Results].

Basically the specimen collection date pill was two different dates, so two rows were placed in grid, and when it was the same date, the Results pill was two different values so it was printing both rows in the grid.  By replacing with the MIN calculations you return one and only one row per patient.

• ###### 2. Re: Calculating row level data based on multiple business logic

Thank you Aaron for looking into it!

For Patient D, where there are multiple specimen collection dates, LOD calc chooses the MIN of those dates and and MIN of the results. Since there is only one result associated with the MIN specimen collection date, Result of "Susceptibility" would be expected for Patient D instead of result of "Resistance".

Thank you,

KA

• ###### 3. Re: Calculating row level data based on multiple business logic

In that case it is choosing Resistance because the MIN is triggered on the alphanumeric [Result], which gives Resistance.  You would need to create a numeric calculated field and assign a rank to each result, and do the MIN of that to get the proper row.

Calculated Field: Result Code

CASE [Result]

WHEN 'Resistance' THEN 1

WHEN 'Susceptible' THEN 0

END

In your [Min Result] calculation, use MIN([Result Code]) instead of MIN([Result]).  The rest should fall into place as is I believe.

• ###### 4. Re: Calculating row level data based on multiple business logic

Thank you Aaron. When I added the new calculated field: Result Code, it gave me the below results in MIN Results (copy) which worked only for patients with same results on same specimen collection date.

• ###### 5. Re: Calculating row level data based on multiple business logic

Hi Kushal,

Could you clarify the

- number of value in "Results" and list them.

- order of each value to determine "minimum"

If you suppose alphabetical order follow below.

Thanks,

Shin

• ###### 6. Re: Calculating row level data based on multiple business logic

Can you attach the workbook again to take a look at your calculations?

• ###### 7. Re: Calculating row level data based on multiple business logic

Hi Shin,

Thank you for taking a look at my question.

- the "Results" column contain the actual results of the patient. For each of the patients, we select the result associated with the MIN Specimen collection date, however, if there are multiple results (Susceptibility and  Resistance) associated with the MIN Specimen collection date for that patient, then we choose Result of "Resistance" (eg in Patient A). The highlighted results in the "Result" column are the only ones I need to keep, rest exclude from the calculation.

Then I need to count those MIN Results to calculate the Numerator. Similar to "Numerator excel" column, which I created in excel. I was able to calculate the Denominator values in tableau which matches with the calculation that I did in excel.

Thank you,

Kushal

• ###### 8. Re: Calculating row level data based on multiple business logic

Here it is. Thank you.

• ###### 9. Re: Calculating row level data based on multiple business logic

Thanks Kushal. I changed it a little bit.

First, in the Result Code calculated field, if you want Susceptible to be the best result instead of Resistance and you are using MIN, you need to switch the codes:

[Result Code]

CASE [Results]

WHEN 'Resistance' THEN 1

WHEN 'Susceptible' THEN 0

END

Next, you need to update the first specimen collection date calculation to a true/false to identify the row that is the first collection date:

[Min (First) Specimen collection date]

[Specimen Collection Date/Time] = { FIXED [Patient ID], [Antibiotic], [Isolate Organism Name] : MIN([Specimen Collection Date/Time]) }

Then you need to create a calculated field to identify the row that is the lowest result code for that date as true/false:

[Min Result Code]

[Result Code] = { FIXED [Patient ID], [Antibiotic], [Isolate Organism Name], [Min (First) Specimen collection date] : MIN([Result Code]) }

Then you add both the MIN fields as filters and keep on the TRUE values.

Last, update the pills in the Rows area:

That should give you the results that are the first specimen collection date, with the lowest result you want to keep.

• ###### 10. Re: Calculating row level data based on multiple business logic

Thank you for your help Aaron.

As I need result of "Resistance" for Patient with multiple results for the same combination of antibiotic and organism, I updated the calculated field [Result Code] and [Min Results Code] accordingly.