First date in table

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, I have a payroll table. And I would like to know when an employee last received an increase.

I’ve pasted below sample data. The hourly rate changed Mar 11/19.
<table width=”1619″>
<tbody>
<tr>
<td width=”93″>First Name</td>
<td width=”118″>Department Code</td>
<td width=”124″>Department Name</td>
<td width=”77″>Home Dept</td>
<td width=”48″>Hourly Rate</td>
<td width=”62″>Day – Effective Date</td>
<td width=”85″>Month – Effective Date</td>
<td width=”74″>Year – Effective Date</td>
<td width=”70″>Salary Rate</td>
<td width=”83″>HourlyPay</td>
<td width=”64″>OvertimePay</td>
<td width=”83″>Commission</td>
<td width=”64″>Bonus</td>
<td width=”56″>AccruedVacPaid</td>
<td width=”64″>Retro</td>
<td width=”97″>SickTimeTaken</td>
<td width=”116″>StatHolidayPay</td>
<td width=”96″>StatWorked</td>
<td width=”64″>Run</td>
<td width=”81″>Payroll Year</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>13</td>
<td style=”font-weight: 400;”>8</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>40.25</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>18</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>27</td>
<td style=”font-weight: 400;”>8</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>39.91</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>19</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>10</td>
<td style=”font-weight: 400;”>9</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>35.75</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>32.93</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>20</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>24</td>
<td style=”font-weight: 400;”>9</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>44.42</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>28.04</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>21</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>8</td>
<td style=”font-weight: 400;”>10</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>35.59</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>50.29</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>22</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>22</td>
<td style=”font-weight: 400;”>10</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>42.59</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>51.25</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>23</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>5</td>
<td style=”font-weight: 400;”>11</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>43.82</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>98.37</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>24</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>19</td>
<td style=”font-weight: 400;”>11</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>62.44</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>115.36</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>25</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>3</td>
<td style=”font-weight: 400;”>12</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>45.25</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>11.97</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>222.57</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>26</td>
<td style=”font-weight: 400;”>2018</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>17</td>
<td style=”font-weight: 400;”>12</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>46.56</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>128.67</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>9.17</td>
<td style=”font-weight: 400;”>1</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>31</td>
<td style=”font-weight: 400;”>12</td>
<td style=”font-weight: 400;”>2018</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>55.16</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>10.29</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>2</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>1</td>
<td style=”font-weight: 400;”>2019</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>54.08</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>87.8</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>3</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>28</td>
<td style=”font-weight: 400;”>1</td>
<td style=”font-weight: 400;”>2019</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>37.17</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>4</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>11</td>
<td style=”font-weight: 400;”>2</td>
<td style=”font-weight: 400;”>2019</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>43.25</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>128.18</td>
<td style=”font-weight: 400;”>33.94</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>5</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>14</td>
<td style=”font-weight: 400;”>25</td>
<td style=”font-weight: 400;”>2</td>
<td style=”font-weight: 400;”>2019</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>55.18</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>10.55</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>6</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>15</td>
<td style=”font-weight: 400;”>11</td>
<td style=”font-weight: 400;”>3</td>
<td style=”font-weight: 400;”>2019</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>58.67</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>56.13</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>7</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
<tr>
<td style=”font-weight: 400;”>Cleopatra</td>
<td style=”font-weight: 400;”>7000</td>
<td style=”font-weight: 400;”>7 – Yorkdale</td>
<td style=”font-weight: 400;”>*</td>
<td style=”font-weight: 400;”>15</td>
<td style=”font-weight: 400;”>25</td>
<td style=”font-weight: 400;”>3</td>
<td style=”font-weight: 400;”>2019</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>57.08</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>133.66</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”>0</td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”></td>
<td style=”font-weight: 400;”>8</td>
<td style=”font-weight: 400;”>2019</td>
</tr>
</tbody>
</table>

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Not sure if this helps display the issue.

Attachments:

You must be logged in to view attached files.

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, additional thought.

It would be even better to see all the pay rates for each employee Ranked.

To see each date we provided you an increase.

 

thanks

[brawnystaffParticipant Started: 0Replies: 4Total: 4]

See attached to get you started…

Attachments:

You must be logged in to view attached files.

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Thanks for the post.

That did the trick.

Can you explain the FIRSTNONBLANK LINE?

I have a pitvottable I created with the sales results. I have some transactions where an item was exchanged. So the value of the net transaction is zero. But it still counts as a transaction and therefore the sales associate is in my list. I would like to filter on transactions <>0.

But you can’t filter measures. IE a months worth of sales for you equal 0 at location 1. So I don’t need you listed. But in location 2 you have sales of $100. So you should be listed.

How to I get the list to only list non zero NET transactions.

[brawnystaffParticipant Started: 0Replies: 4Total: 4]

Regarding explaining the FIRSTNONBLANK line, the measure is similar to a nested formula in that TOPN operator is determining the top maximum value based on the criteria, the FIRSTNONBLANK operator is just ensuring the returned value is not a blank value.

Regarding your second issue with not counting 0 values, if summing values, typically if summing a field 0’s would not be counted.  However, if doing a count, if would affect your results.  Would need you to upload a sample Excel workbook with sample data and expected results..

[Author]

Posts