Do I have to create this table in Script

Wed, 20 May 2015 16:57:17 GMT

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

Re: Do I have to create this table in Script

JonnyPoole — Wed, 20 May 2015 17:30:49 GMT

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

topic Do I have to create this table in Script in QlikView

Do I have to create this table in Script

Re: Do I have to create this table in Script