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 |
|---|
| 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?
