For example, I have a large table with sales information for every month. However, some numbers need to be adjusted because we were open different number of days during some months than others, so the sales numbers need to be multiplied by a factor. I only have the numbers for a month through another month, not for individual months because they don't add properly. So here's what I am trying to do:
When I select the month, I want that to determine what each column in the bigger table is multiplied by. If I select January, it multiplies by some number, if I select January through March, it multiplies by some other number, May through August, September through December, etc. If non-concurrent months are selected then we can ignore this. How can I go about getting the dates to determine a factor?
Thus far, I have it split into a couple tables. The numbers have been changed a little from the original.
First I have:
| Mnth | Month | MnthValue |
| 1 | January | 1.00E+11 |
| 2 | February | 1.00E+10 |
| 3 | March | 1.00E+09 |
| 4 | April | 1.00E+08 |
| 5 | May | 1.00E+07 |
| 6 | June | 1.00E+06 |
| 7 | July | 1.00E+05 |
| 8 | August | 1.00E+04 |
| 9 | September | 1.00E+03 |
| 10 | October | 1.00E+02 |
| 11 | November | 1.00E+01 |
| 12 | December | 1.00E+00 |
and I have:
| MonthSum | Factor |
| 1 | 1.9 |
| 10 | 1 |
| 11 | 1.8 |
| 100 | 1 |
| 110 | 1 |
| 111 | 1 |
| 1000 | 1 |
| 1100 | 1 |
| 1110 | 1 |
| 1111 | 1.7 |
| 10000 | 1 |
| 11000 | 1 |
| 11100 | 1 |
| 11110 | 1 |
| 11111 | 1.6 |
| 100000 | 1 |
| 110000 | 1 |
| 111000 | 1 |
| 111100 | 1 |
| 111110 | 1 |
| 111111 | 1.5 |
| 1000000 | 1 |
| 1100000 | 1 |
| 1110000 | 1 |
| 1111000 | 1 |
| 1111100 | 1 |
| 1111110 | 1 |
| 1111111 | 1.4 |
| 10000000 | 1 |
| 11000000 | 1 |
| 11100000 | 1 |
| 11110000 | 1 |
| 11111000 | 1 |
| 11111100 | 1 |
| 11111110 | 1 |
| 11111111 | 1.3 |
| 100000000 | 1 |
| 110000000 | 1 |
| 111000000 | 1 |
| 111100000 | 1 |
| 111110000 | 1 |
| 111111000 | 1 |
| 111111100 | 1 |
| 111111110 | 1 |
| 111111111 | 1.2 |
| 1000000000 | 1 |
| 1100000000 | 1 |
| 1110000000 | 1 |
| 1111000000 | 1 |
| 1111100000 | 1 |
| 1111110000 | 1 |
| 1111111000 | 1 |
| 1111111100 | 1 |
| 1111111110 | 1 |
| 1111111111 | 1.1 |
| 10000000000 | 1 |
| 11000000000 | 1 |
| 11100000000 | 1 |
| 11110000000 | 1 |
| 11111000000 | 1 |
| 11111100000 | 1 |
| 11111110000 | 1 |
| 11111111000 | 1 |
| 11111111100 | 1 |
| 11111111110 | 1 |
| 11111111111 | 1.05 |
| 1E+11 | 0.45 |
| 1.1E+11 | 0.5 |
| 1.11E+11 | 0.55 |
| 1.111E+11 | 0.6 |
| 1.1111E+11 | 0.65 |
| 1.11111E+11 | 0.7 |
| 1.11111E+11 | 0.75 |
| 1.11111E+11 | 0.8 |
| 1.11111E+11 | 0.85 |
| 1.11111E+11 | 0.9 |
| 1.11111E+11 | 0.95 |
| 1.11111E+11 | 1 |
(the idea here is that January is the first digit, February is the second, etc, so "10" is just November, and "110" is Oct-Nov)
The idea I had was to sum up all of the MnthValue and it will give me MonthSum which corresponds to a factor to multiply the sales by, but there's on issue. I can't figure out how to tell QV to make that connection. I tried creating a variable with the same name as the column MonthSum and defining it as the sum of the MnthValues, but that didn't work.
I'm probably doing this a lot more roundabout and convaluted, so if anyone can help me get the selected months to determine a factor given from a table, I would appreciate it!
