Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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:

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

1 Solution

Accepted Solutions

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.


talk is cheap, supply exceeds demand
6 Replies
Not applicable

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.


talk is cheap, supply exceeds demand
Not applicable

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

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!

Not applicable

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

Not applicable

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

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

Not applicable

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

Thanks a lot! This works like a charm!

Community Browser