Reputation: 45
I have a Rate table that changes Quarter on Quarter.
i.e
+--------+-------+------+
| Q1 | | |
+--------+-------+------+
| Sales | | Rate |
| <1000 | | 1% |
| >=1001 | <2500 | 2% |
| >=2501 | | 3% |
+--------+-------+------+
| Q2 | | |
+--------+-------+------+
| Sales | | Rate |
| <1500 | | 1% |
| >=1501 | <3000 | 2% |
| >=3001 | | 3% |
+--------+-------+------+
etc..
I have created a Quarter field :
if month([booking_date]) = 1 then 1
ELSEIF month([booking_date]) = 2 then 1
ELSEIF month([booking_date]) = 3 then 1
ELSEIF month([booking_date]) = 4 then 2
ELSEIF month([booking_date]) = 5 then 2
ELSEIF month([booking_date]) = 6 then 2
ELSEIF month([booking_date]) = 7 then 3
ELSEIF month([booking_date]) = 8 then 3
ELSEIF month([booking_date]) = 9 then 3
ELSEIF month([booking_date]) = 10 then 4
ELSEIF month([booking_date]) = 11 then 4
ELSEIF month([booking_date]) = 12 then 4
END
And tried all types of ways to make the equivalent:
Qtly Cum. Sales
If Quarter = 1 then sum(Sales) elseif Quarter = 2 then sum(sales) etc...
So i would end up with
+-----------------+-----+-----+-------+-------+-----+------+
| | Jan | Feb | March | April | May | June |
+-----------------+-----+-----+-------+-------+-----+------+
| Sales | 200 | 350 | 500 | 400 | 500 | 700 |
| Qtly Cum. Sales | 200 | 550 | 1050 | 400 | 900 | 1600 |
| rate | 1% | 1% | 2% | 1% | 1% | 2% |
+-----------------+-----+-----+-------+-------+-----+------+
Upvotes: 0
Views: 3188
Reputation: 163
Put your Quarters Field in the columns shelf -- it will partition your months by quarter, but you can hide this header later. Click on your [Sales] pill
Upvotes: 1