5 Replies Latest reply: Oct 10, 2011 4:54 PM by Elizabeth Knight

# Tricky Data Range Question

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!

• ###### Tricky Data Range Question

Hi Elizabeth

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

TypeA:

[

AFrom, ATo, AValue

0, 2500, 1

2501,5000, 2

...

];

IntervalMatch(Test)

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

Hope that helps

Jonathan

Regards

Jonathan

• ###### Tricky Data Range Question

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!

• ###### Tricky Data Range Question

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

• ###### Re: Tricky Data Range Question

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

• ###### Re: Tricky Data Range Question

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