LinkedIn
Twitter
Hi,
I’m currently working on a Look that displays revenue at risk dollar values associated with billing errors for medical claims for services provided within the last 180 days. Most of that is pretty straightforward. However, one claim can have multiple associated billing errors. When that happens, Looker displays a row of data for each error and the expected revenue (revenue at risk) is erroneously duplicated.
I created a group of custom table calculation as defined below to unduplicate the rows by mimicking row_number() over partition in SQL. This works well.
Custom Field Name: Partition
match(${activity_detail.activity_detail_id},${activity_detail.activity_detail_id})
Custom Field Name: Rank
if(${partition}=offset(${partition},-1),1+ row()-${partition}, 1)
Custom Field Name: RN1
${rank}=1
Custom Field Name: Expected Fee Adjusted
if(${rank}=1,${claim_item.item_expected},0)
I hid the Nos in RN1 and all the excess fields from the visualization, leaving only the Month-Year and Expected Fee Adjusted fields visible. I then created another table calculation called Revenue at Risk that sums the Expected Fee Adjusted field.
Custom Field Name: Revenue at Risk
sum(${expected_adjusted})
This gives me one row per claim with a sum of the Revenue at Risk for the entire 180-day period, which is not what I want.
I can pivot on Month-Year, which gets me the right breakouts, and I can use Edit to show just a single row.
But I’m still left with this overly wordy table as you can’t display pivoted data in a line chart.
I know I could approximate the pivot functionality by manually creating Groups for the Month-Year field, but that seems ridiculously tedious since I’ll have to enter one for every month-year combination from 6 months ago through all the years I anticipate the report being used.
Is there another solution to display my unduplicated data summed by month as a line chart?
I don’t have access to LookML, so it must be something I can do via Custom fields, filters, groupings, etc.
Thanks!
