I'm building a table that pulls the Amounts and Invoices for records between two dates based on their Service Date. However, if I get the correct rows pulled the Invoice Links don't show. If I get the Invoice Links to show all the records for the particular Vendor are pulled with the Amounts outside the Date Range as zeros. What's the Invoice Link code that will show only those invoices during that timeframe?
Thanks,
Mindy
Attempt #1
Date Range = 4/1/13 - 7/1/13
Expressions:
Invoice Link =if({$<[Service_Date]={'>=$(vStartDate) <=$(vEndDate)'}>}[Invoice_No] & '<url>' & [Invoice_Link])
Amount =Sum({$<[Service_Date]={'>=$(vStartDate) <=$(vEndDate)'}>}Net_Act_Amt)
Project | AFE# | Invoice Link | Vendor | Service Date | Amount |
---|
BBDF 53H | 121560 | - | Smith & Taylor, Inc | 4/2/13 | 250,000 |
BBDF 54H | 121572 | - | Smith & Taylor, Inc | 5/23/13 | 5,900 |
BBDF 60H | 121211 | - | Smith & Taylor, Inc | 6/15/13 | 275,327 |
BBDF 65H | 131672 | - | Smith & Taylor, Inc | 6/29/13 | 33,185 |
BBDF 71H | 131674 | - | Smith & Taylor, Inc | 7/1/13 | 125 |
| | | | | |
Attempt #2.
Date Range = 4/1/13 - 7/1/13
Expressions:
Invoice Link =[Invoice_No] & '<url>' & [Invoice_Link]
Amount =Sum({$<[Service_Date]={'>=$(vStartDate) <=$(vEndDate)'}>}Net_Act_Amt)
Project | AFE# | Invoice Link | Vendor | Service Date | Amount |
---|
BBDF 20H | 121212 | 16074 | Smith & Taylor, Inc | 12/16/12 | 0 |
BBDF 37H | 121378 | 16070 | Smith & Taylor, Inc | 1/30/13 | 0 |
BBDF 53H | 121560 | 16084 | Smith & Taylor, Inc | 4/2/13 | 250,000 |
BBDF 54H | 121572 | 16138 | Smith & Taylor, Inc | 5/23/13 | 5,900 |
BBDF 60H | 121211 | 16097 | Smith & Taylor, Inc | 6/15/13 | 275,327 |
BBDF 65H | 131672 | 16095 | Smith & Taylor, Inc | 6/29/13 | 33,185 |