Excluding subsets from Charts

[#1]

Hi there,

I'm currently working on a Qlikview project and I'm hoping someone here can help me. I'm quite new to Qlikview so apologies if this problem can be solved in a simple manner that I haven't found. I'm interested in looking at the behaviour of customers who haven't done anything in a certain period. For example imagine I have the following 3 fields; Name, Time, Value.

I can get the sum of the values for people who have shopped in that period using =SUM(if(Time>Start_Time and Time <End_Time, Value)). My question is how would I get the sum of the values for those customers who didn't shop in this time period? Essentially, how I can look at the subset of customers who don't have records between Start_Time and End_time? I know I can do this with SQL on load but I'd like to have interactive charts where the user can define the time period they are interested in.

Thanks for any help you can give me!

[Gysbert_Wassenaar · Accepted solution]

You can probably use set analysis to calculate this. The E() operator (E for Exclude) would be what you need. There's an example here that may help: How to count new /lost entities between two periods. In your case you'd want something like sum({<Customer=e({<Time={'>=$(vStartTime)<=$(vEndTime)'}>}Customer)>}Value) with vStartTime and vEndTime being the variables that users can change to define the interval.