topic Re: P() Function in QlikView

P() Function

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: P() Function

swuehl — Sat, 07 Jan 2017 00:49:50 GMT

No, it's not.

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

is not a valid set analysis expression.

Re: P() Function

Anonymous — Sat, 07 Jan 2017 00:55:45 GMT

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

Regards

Re: P() Function

swuehl — Sat, 07 Jan 2017 10:04:23 GMT

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.

Re: P() Function

Anonymous — Sun, 08 Jan 2017 19:15:49 GMT

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