Dynamic segmentation based on date and sum with agrregation

lorapetrova — Mon, 10 Sep 2018 11:42:24 GMT

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?

topic Dynamic segmentation based on date and sum with agrregation in QlikView

Dynamic segmentation based on date and sum with agrregation