Year to Year with Set Analysis

[#1]

Hi all,

I need to count the record created during the year to year period regarding a date in different fields

To to so I created in the loading script a var :

SET vYearToYear = AddYears(Today(),-1);

In my table I have something like:

IdRecord	T-1	T0	T1	T2	T3	T4	T5	T6
1						14/02/2017	14/02/2017
2	17/04/2015	14/02/2017
3	12/05/2016				07/02/2017
4	11/07/2016	20/09/2016			07/02/2017
5	11/07/2016	20/09/2016			07/02/2017
6	13/10/2016	13/12/2016			07/02/2017
7	25/08/2014	25/03/2015	16/02/2016	13/12/2016	07/02/2017
8	05/11/2016	15/12/2016			07/02/2017
9	29/05/2015	23/05/2016			07/02/2017
10	26/11/2015	25/01/2017
11	24/01/2017
12	01/12/2012	19/01/2016	10/01/2017
13	14/04/2016	30/08/2016	10/01/2017
14	01/09/2015	12/04/2016	N/A	N/A	10/01/2017

Finally I need to get in textboxes the number to [T-1] that hapened during the year to year period, the same for [T0] and so on.

I tried :

Count({$<[T0]={">=$(=vYearToYear)"}>}IdRecord)

It give me 0 for [T-1] when it should be 7

[sunny_talwar]

May be try this:

Count({$<[T0]={">=$(=Date(vYearToYear, 'DD/MM/YYYY'))"}>}IdRecord)

Assuming T0 is formatted as DD/MM/YYYY

[sasikanth]

Could you pleas share that excel file ?

[#4]

Thanks Sunny the date is formated DD/MM/YYYY but still have 0 as the answer

[sunny_talwar · Accepted solution]

What does this give you in a text box?

=Date(vYearToYear, 'DD/MM/YYYY')

May be use dollar sign expansion

Count({$<[T0]={">=$(=Date($(vYearToYear), 'DD/MM/YYYY'))"}>}IdRecord)

[#6]

Here is the excel file

[#7]

Due to the sign expansion Many Thanks Sunny