I would consider using interval matching - one per range type - on Total2. Then I think the problem becomes a lot simpler.
LOAD * Inline
AFrom, ATo, AValue
0, 2500, 1
LOAD * Resident TypeA;
Now just get the value of AValue, BValue, CValue for Limit.
Hope that helps
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!
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!!
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...
LimitTest.qvw 165.5 K
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!!