Dynamic segmentation based on date and sum with agrregation

[lorapetrova]

Hello,

I need some help please, because I cannot solve this issue.

I have a straight table like this one below:

Date	Client	Employee Count	Sale
11.02.2016	Client1	1-9	100
20.03.2017	Client2	50-99	156
25.09.2017	Client3	20-49	8426
10.11.2016	Client4	10-19	564
01.01.2018	Client5	100-199	2956
20.07.2018	Client6	1-9	1500
09.07.2017	Client7	10-19	1548

I have dropdown filter that is loaded like a date island with the following values:

RangeId	From	To
0	-9999.99	9999.99
1	-9999.99	499.99
2	500	999.99
3	1000	3999.99
4	4000	9999.99

I need to have this table as a result where I have selected whether all ranges (RangeId = 0) or specific rage 1,2,3,4 ...

Employee Count	Sum 1-12 month
1-9	1548
10-19	2564
20-49	4259
50-99	3000
100-199	15487

For example:

When I click on RangeID = 2 (500 – 999.99), I need to calculate Sum for last year (-12 month), for Employees Count.

I cannot have correct value in the column {Last year}

I tried different formulas like this:

vDate12 =AddMonths(Today(), -12)

vSumByClient = aggr(sum({<Date={">$(vDate12)<=$(vDate)"}>} Sale ), Client)

or this:

vRange0 = sum($(vSumByClient))

vRange1 =sum(if($(vSumByClient)<='499.99', $(vSumByClient)))

vRange2 =sum(if($(vSumByClient)>='500.00' and $(vSumByClient)<='999.99', $(vSumByClient)))

vRange3 =sum(if($(vSumByClient)>='1000.00' and $(vSumByClient)<='3999.99', $(vSumByClient)))

vRange4 =sum(if($(vSumByClient)>='4000.00', $(vSumByClient)))

=sum(if([RangeId=0, $(vRange0) ,
if(RangeId =1, $( vRange1),
if(RangeId =2, $( vRange2),
if(RangeId =3, $( vRange3),
if(RangeId =4, $( vRange4)
))))))

or this:

=if(sum(GetFieldSelections(RangeId)=1),Sale,
if(sum(GetFieldSelections(RangeId)=2), Sale,
if(sum(GetFieldSelections(RangeId)=3), Sale,
if(sum(GetFieldSelections(RangeId)=4), Sale,
if(sum(GetFieldSelections(RangeId)=0), Sale)))))

It`s works but only when all clients are listed in tablebox. I cannot accumulate the result in a single cell?

How can I get selection from RangeId to show me SUM for Employee Count?