Cannot count the specific year in a table

desmond_mf_fung — Wed, 11 Apr 2018 08:13:40 GMT

Hi All,

Currently, I have a file one column contains "channel" and one column contains "timestamp", the format is "DD/MM/YYYY hh:mm:ss” and I set the loading timestamp TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
Here I would like to count how many records I have per channel in year 2016/2017/last 12 months by channel in a table.

e.g.

Channel	2016	2017
A	xxx	xxx
B	xxx	xxx
C	xxx	xxx
D	xxx	xxx
E	xxx	xxx
F	xxx	xxx

I had tried below expressions, but no luck to achieve. the number it should on 2016 column is exactly the same as 2017 column which is not correct.

First attempt:
2016: Count({$<"year(DATE(DATE#(mid([Timestamp I have],1,8), 'DD/MM/YY'), 'YYYY/MM/DD'))"}= {'2016'}>})[Timestamp I have])

2017: Count({$<"year(DATE(DATE#(mid([Timestamp I have],1,8), 'DD/MM/YY'), 'YYYY/MM/DD'))"}= {'2017'}>})[Timestamp I have])

Second attempt:
2016: Count(DISTINCT [Timestamp I have] & Year(Today()-2))

2017: Count(DISTINCT [Timestamp I have] & Year(Today()-1))

Third attempt:

2016: Count ( {1 <Year = {2016}> } [Timestamp I have])

2017: Count ( {1 <Year = {2017}> } [Timestamp I have])

Fourth attempt:

2016: Count({<[Timestamp I have]={'2016'}>} [Timestamp I have])
2017: Count({<[Timestamp I have]={'2017'}>} [Timestamp I have])

Please help.
Thanks.

Desmond

topic Cannot count the specific year in a table in Connectivity & Data Prep

Cannot count the specific year in a table