Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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!
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!!
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
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!!