topic Re: Sum if Date1 = Date2 (Reference to another field in Set Analysis) in QlikView

Sum if Date1 = Date2 (Reference to another field in Set Analysis)

Thu, 18 Dec 2014 13:38:55 GMT

Hi out there,

perhaps someone can help me with this issue I'm having:

I want to calculate the sales for customers that became new customers a given month and placed orders in following months. So I have two Date-Fields: "NewCustomerMonth" and "SalesMonth"

I tried to summarize the issue in this simplified example:

Data
NewCustomerMonth	SalesMonth	Sales
1	1	200
1	2	100

I want to calculate the revenues after x months passed for all new customers from Month 1.

So I'm looking for the following result:

NewCustomerMonth	Sales in Month1	Sales in Month2	Sales in all Months
1	200	100	300

Instead I get:

NewCustomerMonth	Sales Month1	Sales Month2	SalesTotal
1	300

I have tried these two ways (in for the first column i.e. "Sales in Month1"):

1. sum( {$ < Key = {"=(SalesMonth = NewCustomerMonth)"} > } Sales)

2. sum( {$ < NeukundenKalender._M = p(Kalender._M) > }

with the same result.

How can I get QlikView to sum the Sales "for the SalesMonth equal to the NewCustomerMonth".?

I've also tried plain "IF" but that does not work, because I have not both columns in the Chart (I think it is because of that)

I'd appreciate if anybody could help or has any ideas... mine have run out expect doing it manually........

Thanks

Alejandro

Re: Sum if Date1 = Date2 (Reference to another field in Set Analysis)

anbu1984 — Thu, 18 Dec 2014 13:46:29 GMT

Check this

Re: Sum if Date1 = Date2 (Reference to another field in Set Analysis)

Thu, 18 Dec 2014 15:03:52 GMT

What does NewCustomerMonth means in your example? I can understand that it is a date, but does it show the number of months since our customer is with us? What I can imagine is that you want a table with a dimension like NewCustomerMonth,

expressions be like =sum({<SalesMonth='Month1'>}Sales)

=sum({<SalesMonth='Month2'>}Sales)

last expression: sum(Sales)

It is not the most elegant solution, on the contrary, but it will work. Let me know if it helped you.

Best,

Re: Sum if Date1 = Date2 (Reference to another field in Set Analysis)

Thu, 18 Dec 2014 16:36:44 GMT

Thanks,

NewCustomerMonth is the actual month a customer made its first order, thus becoming a customer. The expressions you proposed do work. But since I have many years of data and need to sum sales "after 3 months of becoming a customer", "after 6 months..." and so on, I was trying to find a way to do it dynamically, in "spoken" formula like this:

Sum of Sales, when SalesMonth = NewCustomerMonth + NewCustomerMonth+1 + NewCustomerMonth+2.

@Anbu Cheliyan: The use of the second time dimension as columns in a pivot does not help me because there are many other Expressions in the table and I still cannot make partial sums (first 3 months, first 6 months after the date NewCustomerMonth)

Regards

Alejandro

Re: Sum if Date1 = Date2 (Reference to another field in Set Analysis)

Wed, 02 Nov 2016 05:10:47 GMT

Hi Alejandro, did you manage to sort this one out at all? I have exactly the same issue...

Re: Sum if Date1 = Date2 (Reference to another field in Set Analysis)

tresB — Wed, 02 Nov 2016 05:23:45 GMT

Hi Richard,

I suggest you to create a new thread with a sample qvw to work on. You would be offered some useful suggestions by people here, I believe.