Identify record/s which is/are present in all periods

[#1]

Dear Community,

I hope you will be able to help me solving a problem - I have searched a lot for a solution but unfortunately I haven't found any.

I want to count records which are present in all (time) periods, think of this simple table:

LOAD * INLINE [

    Year, Customer

    2010, A

    2010, B

    2010, C

    2011, B

    2011, C

    2012, C

];

If you take a look at this table you'll see that the customer "C" will be present in all years, so in this example a Count Distinct (CustomerID for example) would return 1. I've implemented a static solution which is a combination of 3 intersect set analysis (for current 3 years) but this expression would cause heavy troubles if the user wants to analyze an additional year.

Do you have any idea how this could be implemented?

Thanks in advance!

Tom

[#2]

Maby this:

= IF(AGGR(COUNT(DISTINCT Year), Customer) = COUNT(TOTAL DISTINCT Year), 'Yes', 'No')

Cheers

[Gysbert_Wassenaar · Accepted solution]

A straight table with Customer as dimension and as expression sum({<Customer={'=count(distinct Year)=count(total distinct Year)'}>} 1) should show the customers that are present in all the selected years. You can hide the expression if you want.

[#4]

Thanks for your help!

The second solution seems to work but honestly.. I don't know how

I haven't been aware of a set analysis like <Customer={'=count(distinct Year)=count(total distinct Year)'}>

Could you maybe explain this to me?

Furthermore - this will work for a chart where you will be able to set a dimension but what about for example Text objects (it would just state "x Customer(s) are present in all periods"). Is there a possibility as well?

Thanks a lot again!

[#5]

If you would like to see only this customers, use

IF(AGGR(COUNT(DISTINCT Year), Customer) = COUNT(TOTAL DISTINCT Year), Customer, NULL()) 

As the dimension. Then tick "Hide nulls".

Gysbert: How would you use the set expression as a dimension?

Cheers

[#6]

= COUNT(DISTINCT {<Customer={'=count(distinct Year)=count(total distinct Year)'}>} Customer)

[#7]

Thanks a lot! This works like a charm!