Re: Choosing a correct variable for dates using taxt or character functions

Method_Ashur — Tue, 07 May 2013 15:39:19 GMT

Hi Mike,

This is a pretty involved customization. I strongly recommend you speak with a Consultant in more detail regarding this as it goes outside the scope of the forums. On a high level something like this can be done, but when it comes to the details of how, it requires the involvement of a Consultant as they may have a better solution for you.

Let me know whether or not you're interested in speaking with a Consultant regarding this topic and I'll get the ball rolling.

- Ashur

Choosing a correct variable for dates using taxt or character functions

MikeB — Fri, 03 May 2013 12:05:30 GMT

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?

Method:Classic General Q&A

Re: Choosing a correct variable for dates using taxt or character functions

Choosing a correct variable for dates using taxt or character functions