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

[hkkwon89]

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.. However this being a pivot table is it possible to achieve the last column?

I did put in a calculated column to rank them, ToInt(rankasc(assigned_reg_userid,Date_of_visit))

Any help/workaround is appreciated!

[harikm007 · Accepted solution]

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]

@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]

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