I'm looking to have a series of actions where I loop through an invoice table and sum up sales for a quarter.
I'll define the following variables:
- CurrentQuarter - A date function that takes the current month number, divides by 3 and rounds up.
- CurrentYear - A date function that takes the current year. Let's just say for now everything is done within 2013
- Q1Start - 01/01/yyyy for the current year
- Q2Start - 01/04/yyyy for the current year
- Q3Start - 01/07/yyyy for the current year
- Q4Start - 01/10/yyyy for the current year
I'd like to build a CurrentCalcQuarter variable that concatenates the text and the current month in the following way, using the "Join" option for Character actions:
- CurrentCalcQuarter = (Type In) Q + CurrentQuarter, then,
- Current CaleQuarter = CurrentQuarter + (Type In) Start
- I would do the same thing and calculate a variable called NextCalcQuarter
- So, if the CurrentMonth is April, CurrentCalcQuarter is "Q2Start" and NextCalcQuarter is "Q3 Start"
- I would then need to define CalcStartDate and CalcEndDate for the current applicable period. In this case (April), CalcStartDate is "Q2Start" and CalcEndDate is "Q3Start"
How do I use the CurrentCalcQuarter and NextCalcQuarter variables in a Loop through Customer table action so that I can use them for the start and end dates defined above? It's akin to an Excel function referring to a cell that has text in it, and using that text to define the parameters within the formula.
Or is there a better way? I'd rather not have to put in a series of hard-coded IF statements like:
- If Current Quarter = Q1, CalcStartDate = Q1Start.
I'd rather have everything derived.
Am I close?