Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Can you help please?
Looking for expression to count the Ref ID from those ClientID who appear in 2017 only leaving out those who appear in both years.
The result coming form below example should be 3 as D and E appears only in 2017. Any ideas please?
Ref ID | ClientID | Year |
1 | A | 2016 |
2 | A | 2017 |
3 | B | 2016 |
4 | B | 2016 |
5 | B | 2017 |
6 | C | 2016 |
7 | C | 2017 |
8 | D | 2017 |
9 | E | 2017 |
10 | E | 2017 |
Regards
Jacek
=count({<ClientID=P({<Year={2017}>})> - <ClientID=P({<Year={2016}>})>} distinct RefID)
This might help you to work on the equation
Count(If(Aggr(Count(Distinct Year),ClientID)=1,ClientID))
=count({<ClientID=P({<Year={2017}>})> - <ClientID=P({<Year={2016}>})>} distinct RefID)
Let's assume that we only want to work with only 2017; we have years from 2000 to 2017, it won't be pleasant to write all the years as you did in your expression (which works by the way)
How should we tell sense to only count those who have Only(year) = 2017?
Thanks !
Reasonable question... I think this should work:
=count({<ClientID=P({<Year={2017}>})> - <ClientID=P({<Year-={2017}>})>} distinct RefID)
May be like this:
Count(DISTINCT {<[Client ID] = {"=Count(DISTINCT Year) > 0"}>}[Client ID])
Or this:
Count(DISTINCT {<[Client ID] = {"=Count(DISTINCT Year) > 0"}, Year = {2017}>}[Client ID])
It works perfect, thanks.