Set analysis Fiscal YTD

[#1]

I have monthly financial periods starting from 1 March 2011 to 28 Feb 2013 - 1 to 25 (Field is called 'FinncPriod'.

I need to do a set analyis to give me a ytd figure for the period 1 March 2011 to 29 Feb 2012 - periods 1 - 12.

I have an inline load -

MinPeriod	FinncPriod
1	1
1	2
1	3
1	4
1	5
1	6
1	7
1	8
1	9
1	10
1	11
1	12
13	13
13	14
13	15
13	16
13	17
13	18
13	19
13	20
13	21
13	22
13	23
13	24
13	25

This links to my main table 'Journals' on the 'FinncPriod' field.

So for the 1st 12 periods (Mar 2011 - Feb 2012) the MinPeriod is always 1, the next 12 periods (Mar 2012 - Feb 2013) the MinPeriod is always 13. I am disregarding period 13 which was used for take-on balances. I created 2 variables -

vMinPeriod = MinPeriod and

vMaxFinncPriod = Max(FinncPriod).

So I think the set analysis expression (with the selections Year = 2012, Month = Feb)

sum({<MinPeriod={"=$(vMinPeriod)"},FinncPriod={"<=$(vMaxFinncPriod"} ,Drawer={'4'},Month=,Year=> } JrnAmt)

should give me the ytd figure for Mar 2011-Feb 2012 but it doesn't work.

If I change it to

sum({<MinPeriod={"=$(vMinPeriod)"},FinncPriod={"<=12"} ,Drawer={'4'},Month=,Year=> } JrnAmt) I get the correct answer. How can I change the set analysis expression so that I can use the variable vMaxFinncPriod. I'm including my model - Please have a look and advise.

[jonathandienst · Accepted solution]

Danie

I could not find the object with the expression above in your model, but looking at the variables, I suggest that you add an = sign to the definition of vMaxFinncPriod.

Regards

Jonathan

[#3]

Hi Jonathan - Thank you for the prompt reply. I changed it to -

sum({<MinPeriod={"=$(vMinPeriod)"},FinncPriod={"<=$(=$(vMaxFinncPriod))"} ,Drawer{'4'},Month=,Year=> } JrnAmt)

and it worked.

Thank you very much.

Regards

Danie