Using E() in Set Analysis

[pauljohansson]

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

[pgrenier]

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

[pauljohansson]

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)

br

Paul

[pgrenier]

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

[pauljohansson]

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)

br

Paul

[Carlos_Reyes]

Perhaps this will get what you want:

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

[pgrenier]

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

[pauljohansson]

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

[pauljohansson]

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

[Carlos_Reyes]

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 )