Filtering AND + OR (and Exclued Count)

[marleygt]

Hi Community!

Still here with a new question;

I am following a new project with huge data inside.

I tried to create an easy sample (herewith attached) in order to simplify request.

Situation:

Pivot table showing several dimensions as many expressions for calculating different things, (ie %, total sales, current and previous year etc); a separate Tab is showing "Initiative" and "Customers" enlisted in Initiative offered.

Request: need to find the right expression to pick out Customers whom joined more then one Initiative at a time (and also the alternative count).

Example:

in the sample, if you select Initiative n 4, InitiativeName "E" and "F" are the only available; Total Customers joined are 6;

Then if I get specified the Name, I will have 4 Customers for "E" and 4 Customers for "F". Two of them joined both Initiative.

The goal is to know how many customers joined both Initiative (and also identify them).

Take notice of: In the sample could appaer useless cause of few customers and numbers related...in the real project every initiative have many more InitiativeName, and customers are thousands.

I've tried playing with Possible and Excluded count in the expressions; what I've reached is to define which customers joined one Initiative and whic not, (or which joined the other). Not able to define which joined both.

Could you help me please?

Thank you in advance for any help and suggestions!

Alex

[sunny_talwar]

Not sure where you are trying to display this (In the pivot or text box), but a text box solution is may be like this:

Expression for Name

=Concat(DISTINCT If(Aggr(Count(TOTAL <Customers>Customers), Initiative, Customers) <> Aggr(Count(DISTINCT Customers), Initiative, Customers), Customers), ', ')

Expression for Count

=Count(DISTINCT If(Aggr(Count(TOTAL <Customers>Customers), Initiative, Customers) <> Aggr(Count(DISTINCT Customers), Initiative, Customers), Customers))

[marleygt]

Hi sunindia! How are you?

Thanks for fast reply.

Well, to be honest, your solution seems ok in the sample, but is not what I am looking for:

For what concern the "Expression for Name" - it's ok but is not showing correctly;

If I select Initiative n4, it shows "Bob" and "Martha" that are the 2 customers who both joined the initiative, but if I select "F" or "E" (corresponding to Initiative 4), it shows me only one of them, while should still give the info about the 2 customers. (the purpose is filtering customers that only joined more then one initiativeName - subordinated to the Initiative; ---> I don't care if initiative 1 -2 - 3 ...have same customers, but if initiative 1 have 2 or more initiativeName which have more customers, in this case I need to know who and how many they are).

Other problem is converting the expressions suggested, seems they are not able to filter in the project, probably due to the complexity of structure: it give me no result at all (with no errors).

I am still trying to solve the right expression, but at the moment no chance....

[marleygt · Accepted solution]

Here I am,

Happy New Year - in the meanwhile...

Coming back to the post request, I found a very good and easy solution: the And Mode.

It was something I've read in the manual but due to my inexperience I didn't thought could be the right way to find the "and" mode.

So I've read some useful post about andmode and I've reached my desired goal.

Just want to share with you (and in order to close the post).

Thank you for help and suggestion!

See ya in the next post...

Alex