topic Inflow / Outflow between two periods in QlikView

Inflow / Outflow between two periods

Anonymous — Mon, 04 Jun 2018 13:55:27 GMT

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.

Re: Inflow / Outflow between two periods

Anonymous — Mon, 04 Jun 2018 14:47:52 GMT

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.

Re: Inflow / Outflow between two periods

Anonymous — Mon, 04 Jun 2018 18:41:00 GMT

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)

)