topic Re: Using E() in Set Analysis in QlikView

Using E() in Set Analysis

pauljohansson — Fri, 20 Sep 2013 12:54:50 GMT

Hi, If you have experience applying indirect Set Analysis please read. I have found a lot of material on Set Analysis but not on Indirect Set Analysis.

My case:

I want to create a SET consisting of all customers for the CURRENT SELECT that dont exists for currently selected PaymentDate using E(). I manage to create a SET of the customers that belong to currently selected PaymentDate:

count({$<sk_customer = p({$<PaymentDate>} sk_customer)>} sk_customer)

I would assume the inverted would be:

count({1<sk_customer = E({$<PaymentDate>} sk_customer)>} sk_customer)

However, this do not give me any results. Any advice are welcome or where I can find more information on indirect SET analysis.

Thanks in advance,

br Paul

Re: Using E() in Set Analysis

pgrenier — Fri, 20 Sep 2013 13:04:33 GMT

Hello Paul,

I believe what is causing the problem is the "1" set at the start of your expression. Have you considered simply writing the following:

Count({<PaymentDate=E(PaymentDate)>} sk_customer)

Cheers,

Philippe

Re: Using E() in Set Analysis

pauljohansson — Fri, 20 Sep 2013 14:23:23 GMT

Hi Philippe, Thanks for your reply. Unfortunally some sk_customer's dont have a PaymentDate at all. I have a lot of customers that dont have any transaction at all in the PaymentTable - I need them to be included in the SET as well.

I manage to get the correct set by using:

count({1-$<sk_customer = P({$<PaymentDate>}sk_customer)>} sk_customer)

However, this solution dont work since selections in other fields also will be "negated" (because of 1-$). So, as i understand it, I need to solve it using E().

Is it possible to define this SET using E():?

count({1-$<sk_customer = P({$<PaymentDate>}sk_customer)>} sk_customer)

Paul

Re: Using E() in Set Analysis

pgrenier — Fri, 20 Sep 2013 15:10:17 GMT

Well, the exclusion function will return all existing PaymentDates that are excluded by your current selection, but will not cover inexistent entries (customers without any defined payment dates).

Would it be possible to consider defining expected payment dates in the future for those customers during the data load process?

Cheers,

Philippe

Re: Using E() in Set Analysis

pauljohansson — Fri, 20 Sep 2013 15:23:08 GMT

Unfortunally not, a lot of customers simply dont have any payment transactions and will never have.

Do you think its possible to rewrite the following using E()?

count({1-$<sk_customer = P({<PaymentDate>}sk_customer)>} sk_customer)

Paul

Re: Using E() in Set Analysis

Carlos_Reyes — Fri, 20 Sep 2013 15:25:53 GMT

Perhaps this will get what you want:

count( { $<sk_customer = P( { 1 } sk_customer ) - P ( sk_customer ) >} sk_customer )

Re: Using E() in Set Analysis

pgrenier — Fri, 20 Sep 2013 15:27:16 GMT

Would this help?

Count({1} DISTINCT sk_customer)-Count(DISTINCT sk_customer)

So basically count all customers, regardless of the current selection criteria, and substract the number of customers currently selected.

Cheers,

Philippe

Re: Using E() in Set Analysis

pauljohansson — Fri, 20 Sep 2013 15:50:24 GMT

Hi Carlos, thanks for replying. But i dont really understand your reply.

I want to select the ALL sk_customers that dont have any PAYMENTDATE. I assume i need to do a indirect set analsysis.

brPaul

Re: Using E() in Set Analysis

pauljohansson — Fri, 20 Sep 2013 16:00:21 GMT

Philippe, thanks! However, this set "inverts" ALL my selection.

I only want this for sk_customers for selected PaymentDates - Thats why i think i need a indirect Set analsysis.

Conceptually the case is:

I want to be able do different selections in fields and my data shall shrinks as normal. But for my selections in PaymentDate i want the customers that are NOT associated.

br Paul

Re: Using E() in Set Analysis

Carlos_Reyes — Fri, 20 Sep 2013 16:05:38 GMT

Well, that's exactly what my previous post tries to do.

With the "P( { 1 } sk_customer)" you get all the customers that exist in your app.

With the "P( sk_customer)" you get all the customers that exist in your app or in the current selections set.

So, if you subtract the second from the first you should get those customers that don't exist in your current selections set. However if you have no selections you'll get all customers.

count( { $<sk_customer = P( { 1 } sk_customer ) - P ( sk_customer ) >} sk_customer )

Depending on your data model, it may be necessary to add the DISTINCT qualifier in order to count distinct customers:

count( { $<sk_customer = P( { 1 } sk_customer ) - P ( sk_customer ) >} DISTINCT sk_customer )

EDIT:

If you want to be able to filter the customers regarding all other filters, for example to also filter those customers that are not related to a payment date but that are related to a product, then.. instead of using the " {1} " you should disregard the time dimensions that are available for users to do selections:

count( { $<sk_customer = P( { $< PAYMENTDATE=, TimeDim2=, TimeDim3=> } sk_customer ) - P ( sk_customer ) >} DISTINCT sk_customer )

Re: Using E() in Set Analysis

tresB — Fri, 20 Sep 2013 18:03:49 GMT

May be like this?

count({<sk_customer = E({1<PaymentDate>} sk_customer)>} sk_customer)

Re: Using E() in Set Analysis

chematos — Fri, 20 Sep 2013 18:28:48 GMT

Why don't you resolve that in the script?

Create a table with all clients ID, join their PAYMENTDATE or all the info of the fact table and create a new FLAG:

If( isnull(PAYMENTDATE), 0,1) as Payment,

Then you can use the flag in your expression, I think I would do that but I don't know your data model.

Regards

Re: Using E() in Set Analysis

pauljohansson — Fri, 20 Sep 2013 21:48:25 GMT

thanks for all the feedback! Its much appreciated

Ive decided to proceed with changing the DataModel. Now exclusion of PaymentDates includes all Customers.

However, i have one last thing that i dont manage to solve. I think its easy for Set analysis developers.

I now have two exclusion SET statements that works one by one.

Count({<PaymentDate=E(PaymentDate)>} sk_customer)

Count({<PaymentType=E(PaymentType)>} sk_customer)

I dont manage to merge them to one set though. As I understand it this can be done with a intersection operator (*).

Thanks in advance,

Paul

Re: Using E() in Set Analysis

pauljohansson — Fri, 20 Sep 2013 21:50:31 GMT

tresesco, unfortunally, this statement does not return anything. br PAul

Re: Using E() in Set Analysis

Carlos_Reyes — Fri, 20 Sep 2013 22:08:00 GMT

So, what are you trying to accomplish with those two sets?

Now there is a new field : PaymentType

If you want to get those customer that are excluded by your PaymentDate and PaymentType selections, then you should use this:

Count({<PaymentDate=E(PaymentDate), PaymentType=E(PaymentType)>} sk_customer)