Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
shayraber
Creator
Creator

set analysis and minus operation

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

16 Replies
effinty2112
Master
Master

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
Partner - Champion III
Partner - Champion III

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
Creator
Creator
Author

hi Peter,

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

jyothish8807
Master II
Master II

Hi shayraber‌,

Did my solution worked ?

Regards

KC

Best Regards,
KC
shayraber
Creator
Creator
Author

hi KC,

Nope. It didn't unfortunately...

See the solution below.

Marked as Correct Answer

shayraber
Creator
Creator
Author

thanks!

that what I needed

shayraber
Creator
Creator
Author

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?