cancel
Showing results for 
Search instead for 
Did you mean: 

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

hkkwon89
9 - Travel Pro
9 - Travel Pro

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.

hkkwon89_0-1661186465092.png

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!

 

1 ACCEPTED SOLUTION

harikm007
13 - Data Warehouse
13 - Data Warehouse

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.

harikm007_0-1661187910818.png

-Hari

 

View solution in original post

3 REPLIES 3

harikm007
13 - Data Warehouse
13 - Data Warehouse

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.

harikm007_0-1661187910818.png

-Hari

 

@harikm007 Hi Hari,

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

hkkwon89_0-1661273523394.png

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
13 - Data Warehouse
13 - Data Warehouse

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.

harikm007_0-1661330793606.png

-Hari