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
