Do I have to create this table in Script

[#1]

I need to create 2 reports/query that shows

     1) Inactive Customers with a Credit Limit

     2) Customers with no Sales in last 6 months

Both queries rely on a "cutoff date" = Today-6 months

On #1, I want to show all customers with Max(InvoiceDate) <= Cutoff Date and CreditLimit >1

On #2, I want to show all customers with Sales=0, with MaxInvoiceDate >=CutoffDate

I have been trying to do this in Pivot Table with calculated dimensions, but am having problems

Dimensions:

CustNo

CusName

Credit Limit ->  If(CreditLimit >1, CreditLimit, '') -> Suppress Null Values

MaxInvDate -> =(Date(max(InvDate)  ->  //ERROR IN CALCULATED DIMENSION

It seems I am stepping on my toes.   If I create an expression for Max(InvDate) i get a value

It seems to me, I need to setup these filters before the dimensions are returned.  

What am I missing?  If I put the MaxDate into n If statement in expressions, I still get all the customers but if the MaxDate is outside my parameter, it just show null

[JonnyPoole]

Calculated dimensions usually don't like chart aggregation functions like Max() unless you used advanced aggregation function (aggr) to subtotal it to a specific dimension granularity

MaxInvDate -> =(Date(max(InvDate)  ->  //ERROR IN CALCULATED DIMENSION

try something like this where you aggregate based on the other chart dimensions:

Date(   aggr( max(InvDate) , CustNo,CustName)  )