set analysis and minus operation

[shayraber]

hi all,

I have a set of customer IDs in my document.

the ID is only for active customers per month.

the time resolution in my document is a month.

for example, in November I had 1000 customers, in December 10 new customers added and 25 became inactive so, the customers amount in December is 985.

what I wish to have is some object/expression which will show me per each month, which are the customers that became inactive (the 25 customers in December in my example).

is there a way of doing it via set analysis? or only at the script?

please assist,

thanks

[effinty2112 · Accepted solution]

Hi Shay,

It's difficult to give you an expression that will work for you while knowing so little of your application but maybe something like this might work for you.

In your application replace EntryMPeriod with your month field and replace 201511 and 201510 with this month and the previous month respectively

=Concat(DISTINCT {$

<Customer = P({<EntryMPeriod = {201510}>})>-<Customer = P({<EntryMPeriod = {201511}>})>

}Customer,',')

In my application this lists customers who placed orders in October but not in November, becoming, by your definition, inactive.

[Peter_Cammaert]

An important distinction:

If a customer is active in a particular month, then your field for that month will have a specific value, but if a customer is not active in another month will

• the field contain a real NULL value for that month, or
• will there be no field value (and no customer data) for that month at all?

QlikView cannot invent data on-the-fly when there is none. No data in a particular month means no customer ID for that month.

[shayraber]

hi Peter,

it's not a real NULL but a no field value

[jyothish8807]

Hi shayraber‌,

Did my solution worked ?

Regards

KC

[shayraber]

hi KC,

Nope. It didn't unfortunately...

See the solution below.

Marked as Correct Answer

[shayraber]

thanks!

that what I needed

[shayraber]

Thank you all for your replies,

Andrew made it and gave me the closest expression to what i looked for.

I used this:

=count(DISTINCT {$<SITE_ID = P({<PERIOD_CODE = {201510}>})>-<SITE_ID = P({<PERIOD_CODE = {201511}>})>

}if(isnull(key)<>-1,SITE_ID))

key is a field I'm using and only SITE_IDs (e.g. customer ID) which is active in a certain period will have a value in it.

now,

I've tried but didn't succeeded to make is generic (within an expression). the solution is hard-coded and I'm trying to make it dynamic.

Any ideas?