6 Replies Latest reply: May 22, 2013 11:44 AM by tnielson

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

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:

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?

Tom

• ###### Re: Identify record/s which is/are present in all periods

Maby this:

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

Cheers

• ###### Re: Identify record/s which is/are present in all periods

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.

• ###### Re: Identify record/s which is/are present in all periods

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!

• ###### Re: Identify record/s which is/are present in all periods

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

• ###### Re: Identify record/s which is/are present in all periods

Thanks a lot! This works like a charm!

• ###### Re: Identify record/s which is/are present in all periods

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