Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I have a very simple question, that I just can't get my head around on how to solve.
I load a dataset that basically looks like this:
Period CustomerID Sales
201801 A 500
201801 B 350
201802 B 150
201802 C 200
So what I would like to see is a pivot table that looks like this:
Total sales current period Total sales prior period In Flow (new customers) Outflow (customers left)
350 850 200 500
So I'm trying to figure out how I can do a set analysis based on the CustomerID that did not exist in the prior period to calculate InFlow and Customers that did exist in the prior period, but not anymore for Outflow.
In SQL I could obviously full outer join the two datasets and compare current vs prior periods, but I would like to do it in Qlikview.
Any suggestions would be extremely appreciated.
I think I got a bit closer to the solution. I'm thinking about implementing something like this:
sum ({<CustomerId=p({<Period={'$(=only(Period))'}>}CustomerId), CustomerId=e({<Period={'$(=only(Period)-1)'}>}CustomerId)>} Sales)
So basically I set a CustomerId list equal to a list of all customers that exist in the current Period but exclude (e function) customers that existed in the prior period. This is for Inflow (New Customers).
Now the only thing that is still left unclear to me how I select the customers that appear in BOTH periods and exclude the customers that appear in only one period. So basically I only want to return customer B.
Should I do it like this? Calculate the total sales of all periods combined and then subtract the excluding datasets from it? Can it not be done easier / cleaner?
sum(Sales)
-
(
sum ({<CustomerID=p({<Period={201802}>} CustomerID), CustomerID=e({<Period={201801}>}CustomerID)>} Sales)
+
sum ({<CustomerID=p({<Period={201801}>} CustomerID), CustomerID=e({<Period={201802}>}CustomerID)>} Sales)
)