Tricky Data Range Question

[#1]

Hi Everyone,

I am working on a pivot table that will calculate a value, compare that value to a range, and based on where the calculated value falls in the range, display a final value on the report.  I'm going to try to explain this using an example:

I have 4 ranges, one for each 'Type':

Type A Range:
MinRange	MaxRange	Limit
0	2500	1
2501	5000	2
5001	7500	3
7501	10000	4

Type B Range:
MinRange	MaxRange	Limit
0	5000	1
5001	10000	2

Type C Range
MinRange	MaxRange	Limit
0	3000	1
3001	6000	2
6001	9000	3
9001	12000	4

Combined Range
MinRange	MaxRange	Limit
0	2000	1
2001	4000	2
4001	6000	3
6001	8000	4
8001	10000	5
10001	12000	6

The final report should look like this:

Center	Type	Month1	Month2	Total1	Total2	Limit	Remaining
1	A	0	0	0	5000	2	2
1	B	0	0	0	5000	1	1
1	C	1	0	1	5000	2	1
1	Total	1	0	1	5000	3	2
2	A	0	1	1	3500	2	1
2	B	0	1	1	3500	1	0
2	C	0	0	0	3500	2	2
2	Total	0	2	2	3500	2	0
3	A	0	0	0	9000	4	4
3	B	0	0	0	9000	2	2
3	C	2	0	2	9000	3	1
3	Total	2	0	2	9000	5	3

Where:

- 'Total1' is a total of the values in 'Month1' and 'Month2' for each 'Type'

- 'Total2' is a total of production, completely separate from all previous information and is calculated by summing the aggregate of production by Center only, using NoDistinct

- 'Limit' is to be determined by locating where 'Total2' falls in the ranges above for each type, and using the combined range for the total line.

- 'Remaining' is 'Limit' - 'Total1'

Right now, I have a lookup table for the ranges, keyed off of 'Type' and with a Sequence Number to identify each line. I have tried several things, and I believe I have gotten the closest with the following:

If(Count({$<"$vTotalProduction" = {">$(=VMSMinRange) <$(=VMSMaxRange)"}>} Distinct VMSSeqNo) = 1, VMSLimit)

In the above set analysis, vTotalProduction = the same equation used to calculated 'Total2'.  However, because I haven't figured out how to isolate the 'Type' in this set analysis, I have to make a selection in Type to narrow it down, and this still counts all of the range values for that type rather than isolating the one record where vTotalProduction is within the range.

Has anyone ever encountered something like this?  Any help would be greatly appreciated!

[jonathandienst]

Hi Elizabeth

I would consider using interval matching - one per range type - on Total2. Then I think the problem becomes a lot simpler.

TypeA:

LOAD * Inline

[

     AFrom, ATo, AValue

     0, 2500, 1

     2501,5000, 2

...

];

IntervalMatch(Test)

LOAD * Resident TypeA;

Now just get the value of AValue, BValue, CValue for Limit.

Hope that helps

Jonathan

Regards

Jonathan

[#3]

Hi Jonathan,

I believe I know how to do what you're suggesting, and I'm going to try and see if I can get this to work.  However, if possible I would like to avoid having to determine Total2 in the load script.  We have a large dataset already, so I'm trying to do what I can to avoid growing the size of the file.  Thank you for your help!

[#4]

Alright,

I have tried using interval match in the load script, but I'm afraid it is going to be too cumbersome.  The problem I am running into is that I will need to calculate every possible Total2 amount that could occur from the user's date selection, and then use that in the interval match.  I really need to find away where Total2 can be compared to the range when the selection is made, to avoid loading a lot of extra data into the script.  Are there any other ideas out there?  Thank you for your help!!

[jonathandienst · Accepted solution]

Elizabeth

I think he attached example does what you want, except for the combined limits - I have not figured out a way to do them - but this is a start...

Regards

Jonathan

[#6]

Thank you, Jonathan, this was a huge help!  I just had to change a few things to fit my actual data model, but this pulled exactly what I needed for the detail information.  I was not able to get this to work for the total, either, so what I did was add "total" records to my actual data and treated them like normal detail data.  This means I wasn't able to completely avoid adding additional records to the data model, but the size increase was miniscule, especially compared to the other tries I had attempted.

Thank you again for working on this!!