2 Replies Latest reply: Apr 22, 2012 12:27 PM by DaniePosthumus

# Set analysis Fiscal YTD

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.
• ###### Set analysis Fiscal YTD

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

• ###### Re: Set analysis Fiscal YTD
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