Forum Discussion

hkkwon89's avatar
hkkwon89
Cloud Apps
08-22-2022
Solved

How do I display the earliest date as a column in a pivot table?

Hi, I need to create a healthcare pivot table with three columns.  Provider Name, number of claims by provider, and earliest date of encounter for each provider. Something like above.. How...
Dashboards & Reporting
Data Models
Healthcare
  • harikm007's avatar
    harikm007
    08-22-2022

    Hi hkkwon89 ,

    One option is using Rank function in widget.

    Create a value panel with formula something like this:

    rank(max([Days in Date]),"ASC","1223",[provider])

    Then apply filter on value panel to display only the rank = 1.

    Finally disable the value panel.

    -Hari

     

harikm007's avatar
harikm007
Data Warehouse
08-22-2022

Hi hkkwon89 ,

One option is using Rank function in widget.

Create a value panel with formula something like this:

rank(max([Days in Date]),"ASC","1223",[provider])

Then apply filter on value panel to display only the rank = 1.

Finally disable the value panel.

-Hari

 

hkkwon89's avatar
hkkwon89
Cloud Apps
08-23-2022

harikm007 Hi Hari,

Thank you for your response; however, I'm having some issues implementing your solution.

First, this being a pivot table it's requiring some kind of aggregation on the date field as you can see on the left hand panel for DATE_OF_VISIT. 

Second, I'm not sure how you achieved the Date Rank you created using 

rank(max([Days in Date]),"ASC","1223",[provider])

to be as a widget filter? I am only able to choose the existing columns in my data set as a filterable item.

Thank you

 

  • harikm007's avatar
    harikm007
    Data Warehouse
    08-24-2022

    1. In pivot we cannot display date field after a value/measure panel. So may be you can use Aggregated Table plugin instead of pivot.

    2. To add 'Date Rank' as widget filter - enable the panel, click on filter button on 'Date Rank' panel and choose the filter condition.

    -Hari