Compare Min/Max for Bucket

[Anonymous]

Hi

We have requirement for comparing a aggregated value on front end compare it with Min and Max and find the bucket(this bucket would be the Dimension for a chart)

Bucket Table would be

Row Num	Min	Max	Bucket
1	0	5	0-5
2	6	10	6-10
3	11	20	11-20

And Value aggregated on front end would be compared with min and max and Bucket should be selected e.g. value is 3 than 0-5 bucket would be selected.

This Bucket would be used as dimension in a chart.

Please advise how this can be implemented.

Regards,

ASP

[Anonymous]

Antonio,

5 you have mentioned in aggregation is static, the Band can be 0-5,6-10,11-20

[Anonymous]

Yes Because Aggregation will happen only on Front end i cannot count and bring the Value as it will restrict slice and dice

[antoniotiman]

5 isin't static. Class Aggregates as You want.

Add Value to Data and try.

[antoniotiman]

See below Example

Load * Inline [
Row Num,Provider ID,Patient ID
1,AA,11
2,AA,12
3,AA,13
4,BB,21
5,CC,1
6,CC,2
7,CC,3
8,CC,4
9,CC,5
10,CC,6
11,CC,7
];

[Anonymous]

No I Mean my requirement is as Below

Bucket should be 0-5, 6-10, 11-20(here it is band of 10), so how would i have band of 5, 10 and 25 in single expression.

Also cannot write nested If as solution should be Dynamic, if new Client comes Band should be according to that client which is configured in Bucket Table, we can also change Structure of Bucket Table that's something we designed for our requirement.

[antoniotiman]

See Attachment

[Anonymous]

This is good but if i have another band 20-35(Band of 15) we need to have nested If or there is any other solution without that

[antoniotiman]

See Attachment

[Anonymous]

Toni, thanks for your help, but this is what i had done initially but issue is this becomes static, now if i have another client and there Band is different than this logic wouldn't work, based on which client is looking at report Band should change, thats why we had created the Bucket Table where we inserted Band for Each client, but i was not able to compare to Min and Max(Same If condition you wrote).

[effinty2112]

Hi Anwar,

Maybe a calculated dimension like:

=Aggr(

Pick(

Match(-1,

Count(DISTINCT [Patient ID]) <=5,

Count(DISTINCT [Patient ID]) <=10,

Count(DISTINCT [Patient ID]) <=20),

'0-5',

'6-10',

'11-20')

,[Provider ID])

Good luck

Andrew