Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Adding Date Specific Modifiers

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:

MnthMonthMnthValue
1January1.00E+11
2February1.00E+10
3March1.00E+09
4April1.00E+08
5May1.00E+07
6June1.00E+06
7July1.00E+05
8August1.00E+04
9September1.00E+03
10October1.00E+02
11November1.00E+01
12December1.00E+00

and I have:

MonthSumFactor
11.9
101
111.8
1001
1101
1111
10001
11001
11101
11111.7
100001
110001
111001
111101
111111.6
1000001
1100001
1110001
1111001
1111101
1111111.5
10000001
11000001
11100001
11110001
11111001
11111101
11111111.4
100000001
110000001
111000001
111100001
111110001
111111001
111111101
111111111.3
1000000001
1100000001
1110000001
1111000001
1111100001
1111110001
1111111001
1111111101
1111111111.2
10000000001
11000000001
11100000001
11110000001
11111000001
11111100001
11111110001
11111111001
11111111101
11111111111.1
100000000001
110000000001
111000000001
111100000001
111110000001
111111000001
111111100001
111111110001
111111111001
111111111101
111111111111.05
1E+110.45
1.1E+110.5
1.11E+110.55
1.111E+110.6
1.1111E+110.65
1.11111E+110.7
1.11111E+110.75
1.11111E+110.8
1.11111E+110.85
1.11111E+110.9
1.11111E+110.95
1.11111E+111

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