Relate different Fields in Set Analysis for YTD Calculation

[#1]

Hey guys,

I’m trying to calculate Year-to-Date (YTD) data, but I’m stumbling on the input format of the months. What happens is that the months are input into my QVW in numbers, such as 1 ,2, 3… where 1 refers to January, 2 refers to February, and so on.

In order to make my file more visually appealing, I wanted to show the actual month names in the Selection List. I did this by using the following Load Inline table:

The difficulty lies in the fact that, in this particular company, the YTD is calculated from April to March of the next year. Therefore, I need to make another transformation, linking April to 1, May to 2, June to 3 and so on. I tried to do this using a Load Inline table once again.

When the user selects a month in the modMonth List, I need to take this selection to my Set Analysis (using GetFieldSelection), and “translate” it to it’s orderMonth correspondent, inside the SAME SET ANALYSYS EXPRESSION.

I don’t know how to make this link between the value I get in the selection and it’s correspondent that will be used in the calculation. Can anyone help me do this inside Set Analysis?

[swuehl]

The fields are already associated being in the same table, so if you select a modMonth, there will be only one possible value for orderMonth.

Not sure how your expression looks like, but I assume the bigger issue is handling year bounderies.

[ecolomer · Accepted solution]

See this posts:

Fiscal Year

and

Fiscal and Standard Calendar generation