P() Function

[Anonymous]

Hi everyone, hope someone can help me. Is this a correct way of  using the P function?

Sum({$<Customer=P({<Min(TOTAL <Customer> InvoiceDate)>=AddMonths(MakeDate(CalendarYear, CalendarMonth), -2)>} Customer)>} InvoiceAmount)

I mean, can I use the Min(TOTAL <Customer> InvoiceDate)>=AddMonths(MakeDate(CalendarYear, CalendarMonth), -2)  ?

Regards.

[swuehl · Accepted solution]

No, it's not.

{<Min(TOTAL <Customer> InvoiceDate)>=AddMonths(MakeDate(CalendarYear, CalendarMonth), -2)>}

is not a valid set analysis expression.

[Anonymous]

Ok thank you so is there a workaround to achive this?

Regards

[swuehl]

To achieve what?

Sorry, but it's unclear what you want to achieve finally.

Maybe you can use an advanced search:

Sum({$<Customer= {"=Min(InvoiceDate) >=AddMonths(MakeDate(CalendarYear, CalendarMonth), -2)"} >} InvoiceAmount)

But I am not sure what your context is, e.g. the value of CalendarYear and CalendarMonth in the context Customer. Both fields need to show only one single value per Customer.

[Anonymous]

Ok. I understand. What I wanted to achive is a pivot table to show the invoices amount given a month and a year but only for those customers which first invoice is in the period of the two previous months and the selected one. Like this one.

Finally I got it mapping in the load script the first invoice date and using the following expression.

Sum( TOTAL AGGR(Sum({<FirstInvoiceDate={'>=$(=AddMonths(MakeDate(CalendarYear, CalendarMonth),-2))'}>}   InvoiceAmount), CalendarYear, CalendarMonth, Customer))

Thanks a lot for your time

Regards