topic Re: Sum of Invoices of a month where first date is in between that month and the two previous in QlikView

Sum of Invoices of a month where first date is in between that month and the two previous

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

Hi everyone, hope someone can help me cause I've been trying to achieve this for days and I couldn't get it.

What I'm trying to get is a pivot table with two dimensions; Year and Month. The expression I need is the sum of the invoices amount of all the customers which first date of invoice is between the last day of the month of the dimension and the first day of the two previous months of that value of the dimension. So for the Nov-2016 I need all the invoices in November which first Invoice Date is between 01/09/2016-30/11/2016.

For example if I have the following values

the pivot table I need is this one:

Thanks in advance for your time.

Re: Sum of Invoices of a month where first date is in between that month and the two previous

sunny_talwar — Wed, 04 Jan 2017 02:44:03 GMT

Your raw data looks like the 1st screenshot??

Re: Sum of Invoices of a month where first date is in between that month and the two previous

Anonymous — Wed, 04 Jan 2017 02:52:13 GMT

Sorry Sunny, I don´t understand pretty well what you're asking. In the 1st screenshot e.g. the value of 500 means the total sum of the invoices for the Customer A in the month of sep-2016. They are example values. Is that what you meant?

Regards

Re: Sum of Invoices of a month where first date is in between that month and the two previous

sunny_talwar — Wed, 04 Jan 2017 02:54:16 GMT

What I am trying to understand is how does your raw data looks like? Is it like this?

Re: Sum of Invoices of a month where first date is in between that month and the two previous

Anonymous — Wed, 04 Jan 2017 03:07:44 GMT

Ah ok, I get it. Excuse me again. No, they would be like this. The bold date means the first Invoice Date

Regards

Re: Sum of Invoices of a month where first date is in between that month and the two previous

sunny_talwar — Wed, 04 Jan 2017 03:27:55 GMT

Something like this?

Re: Sum of Invoices of a month where first date is in between that month and the two previous

Anonymous — Wed, 04 Jan 2017 03:54:09 GMT

Yeah!! That's is what I'm looking for. I migth be wrong but I think I must use the P function to select all the customers with the first date in that period and get the sum of their invoices. What you think?

Re: Sum of Invoices of a month where first date is in between that month and the two previous

sunny_talwar — Wed, 04 Jan 2017 09:46:16 GMT

I just used Sum(Amount)... I am not sure I understand your logic related to the first date. Would you mind expanding a little on this?

Re: Sum of Invoices of a month where first date is in between that month and the two previous

Anonymous — Wed, 04 Jan 2017 17:30:13 GMT

Ah ok, I see. My fault, I didn't notice the difference between you table and mine. For example, if you take a look at december in this table, you'll see a total amount of 400. In yours It is 750. That's because i don´t include Customer A and Customer B given the fact that their first invoice date belong to september, and so they are not in the period of oct-nov-dec. PD.This table has the value of Dec correct.

Regards

Re: Sum of Invoices of a month where first date is in between that month and the two previous

sunny_talwar — Wed, 04 Jan 2017 19:50:21 GMT

May be this?

=Sum(Aggr(If(Min(TOTAL <Customer> Date) >= AddMonths(MakeDate(Year, Month), -2), Sum(Amount)), Year, Month, Customer))

Re: Sum of Invoices of a month where first date is in between that month and the two previous

Anonymous — Wed, 04 Jan 2017 21:23:25 GMT

Yeah It works. Thank you very much.