Forum Discussion

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.. 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!

dashboards & reporting

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

3 Replies

Replies have been turned off for this discussion

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