Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Maby this:
= IF(AGGR(COUNT(DISTINCT Year), Customer) = COUNT(TOTAL DISTINCT Year), 'Yes', 'No')
Cheers
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.
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!
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
= COUNT(DISTINCT {<Customer={'=count(distinct Year)=count(total distinct Year)'}>} Customer)
Thanks a lot! This works like a charm!