topic Re: set analysis and minus operation in QlikView

set analysis and minus operation

shayraber — Mon, 21 Dec 2015 10:06:28 GMT

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

Re: set analysis and minus operation

marcus_sommer — Mon, 21 Dec 2015 11:03:01 GMT

You will need some kind of logic to identify those customer. Maybe something like this:

count({< Customer = {"=sum(sales)>0"}>} DISTINCT Customer)

- Marcus

Re: set analysis and minus operation

Kushal_Chawda — Mon, 21 Dec 2015 11:09:08 GMT

is there any Flag to recognize inactive customers?

Re: set analysis and minus operation

tresB — Mon, 21 Dec 2015 11:11:47 GMT

Since you asked about minus operation, guessing you have a flag to active customer. If so, you can try something like:

=Count({<CustomerFlag -={'Active'}>} Distinct Customer) //Note the '-' symbol

Or, if you 'InActive' flag too, you can use simpler option like:

=Count({<CustomerFlag ={'InActive'}>} Distinct Customer)

Re: set analysis and minus operation

shayraber — Mon, 21 Dec 2015 11:22:26 GMT

hi all,

thank you all for your replies.

I don't have any flag for inactive customers and neither an indication like sales value.

I have a source table with aggregated customers data per customer and month. I filter only customers with filed the status equals "Active". hence, I have records for each month. if a customer becomes inactive - it means I'll filter out his data from that month and so on.

by "minus" i meant that i want to implement the minus operation in SQL which allows me to get a record set which included in one data set and do NOT included in another.

is it possible with set analysis? or i need to do it via script only?

Re: set analysis and minus operation

Mark_Little — Mon, 21 Dec 2015 11:33:00 GMT

Hi,

Would depend on your data.

What defines the types of customer . i.e. Active, Inactive.

Existing customer / new customer.

There will be a way to approach this in the set analysis and script, but would need a better understanding first.

Mark

Re: set analysis and minus operation

jyothish8807 — Mon, 21 Dec 2015 11:36:28 GMT

Hi Shay,

As you said:

"the ID is only for active customers per month."

This means if a user is inactive he will not have any ID, in this case create a expression:

count(Distinct If(isnull(ID)<>-1,CustomerID))

In Dimension:

Month

You can create a Bar or straight chart.

Regards

Re: set analysis and minus operation

shayraber — Mon, 21 Dec 2015 13:06:43 GMT

that's correct BUT i want to get the customer's IDs.

getting how many customers became - that's i know.

the catch is to get their IDs... (concat for example, not count)

Re: set analysis and minus operation

shayraber — Mon, 21 Dec 2015 13:11:02 GMT

hi,

I have a field for active customers.

if a customer is active in month X and inactive in month X+1, than he will have a certain value in that field for month X and null for month X+1

Re: set analysis and minus operation

jyothish8807 — Mon, 21 Dec 2015 14:03:24 GMT

Hi ,

Try this:

If(isnull(ID)<>-1,concat(Distinct CustomerID,';')))

Regards

Re: set analysis and minus operation

effinty2112 — Mon, 21 Dec 2015 14:22:20 GMT

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,',')

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

Re: set analysis and minus operation

Peter_Cammaert — Mon, 21 Dec 2015 15:18:46 GMT

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.

Re: set analysis and minus operation

shayraber — Tue, 22 Dec 2015 07:27:01 GMT

hi Peter,

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

Re: set analysis and minus operation

jyothish8807 — Tue, 22 Dec 2015 08:32:12 GMT

Hi shayraber‌,

Did my solution worked ?

Regards

Re: set analysis and minus operation

shayraber — Wed, 23 Dec 2015 11:12:23 GMT

hi KC,

Nope. It didn't unfortunately...

See the solution below.

Marked as Correct Answer

Re: set analysis and minus operation

shayraber — Wed, 23 Dec 2015 11:12:52 GMT

thanks!

that what I needed

Re: set analysis and minus operation

shayraber — Sun, 27 Dec 2015 08:44:08 GMT

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?