I am working with the following data being uploaded into Qlik:
| Quarter |
Achieved Sales |
Sales Goal (fixed) |
Allocated points (fixed) |
| Q1 |
$100 |
$200 |
50 |
| Q2 |
$50 |
$200 |
50 |
| Q3 |
$150 |
$200 |
50 |
Using this data, I created the following table in Qlik:
| Quarter |
Points |
| Q1 |
25 |
| Q2 |
12.5 |
| Q3 |
37.5 |
Points = (Achieved Sales/Sales Goal) * Allocated Points ie. Q2 Points = (50/200)*50 = 12.5
This is what the actual expression for the Points column in this table looks like in Qlik (I am using the sum function with the fields because in the full data set there are multiple different achieved sales in each quarter which need to be aggregated for this calculation):
= Sum([Achieved Sales])/Sum([Sales Goal]) * [Allocated Points]
However, I now want to create a table with the same columns as table 2 but using YTD/aggregated values (except for Allocated Points) from table 1 when calculating the Points column. So I want the table to look like this:
| Quarter |
Points |
| Q1 |
25 |
| Q2 |
18.75 |
| Q3 |
25 |
Points = (YTD Achieved Sales/YTD Sales Goal) * Allocated Points ie. Q2 Points = (150/400)*50 = 18.75
I tried using the following expression for the Points column in Qlik using set analysis:
=If(Quarter='Q1', Sum([Achieved Sales])/Sum([Sales Goal]) * [Allocated Points],
If(Quarter='Q2', Sum({$<[Quarter]={'Q1', 'Q2'}>}[Achieved Sales])/Sum([Sales Goal] * 2) * [Allocated Points],
If(Quarter='Q3', Sum({$<[Quarter]={'Q1', 'Q2', 'Q3'}>}[Achieved Sales])/Sum([Sales Goal] * 3) * [Allocated Points])))
However, this expression is not returning the values that I am trying to achieve. After removing the additional multiplication being done with the Sales Goals, I found that the resulting values were the same as the values in table 2, which means that the set analysis is not working as I intended. Despite specifying the Quarters in the expression, the expression only calculates the values in Points column using the Quarter that is in the Quarter column.
How do I aggregate the values being used in calculating this column while keeping the column split out into a row for each quarter?
