Inflow / Outflow between two periods

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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)

)