How to ignore selection in a if Statement with Set Analysis?

[#1]

I have to calculate fiscal YTD? Our fiscal year starts from July to August. So, If i select Month = May 2012..then it has to calculate from July 2011- May 2012. I have the following formula, but the moment I select time dimensions, it filters that data to that month.

=if(date(CalMonthYear, 'M')>= 7,

sum (  {< CalMonthYear={">=$(=makedate(year(max(CalMonthYear)), num('7'))) <=$(=Max(CalMonthYear))"}, CalMonthYear=>} Direct_Cost),0)

Any Help?

Thanks.

[amars]

Hi Vani,

Can you calculate the Quarter Start Date and End Date in two variables and use it inside ur expressions.

The issue is the > expression always returns month Jul since you have used Num('7'), also the use of Max in < will always returns the selected month and thus your chart shows incorrect values.

If u wish to try put two labels and put the expressions and check.

Try using the below expression

sum (  {< CalMonthYear={">=$(=QuarterStart(CalMonthYear) <=$(=QuarterEnd(CalMonthYear))"}, CalMonthYear=>} Direct_Cost)

Thanks...

[#3]

Thanks Micheal.

Since our fiscal year starts from July- I always want it to calculate from July till the month I selected.  So,

1)if I select April 2012 then it has to calculate from May 2011-April 2012.

2) If I select October 2012. The it has to calculate from July 2012-October 2012.

Any Help.

Thanks

[tresesco]

Could you post a sample?

[#5]

Hi,

Here is the sample file. If I select March 2013, it should show data only from July 2012-March 2013.  This is my formula:

=if((only({<FiscalFlag=, CalMonthYear=>}FiscalFlag)= 1),

sum (  {< CalMonthYear={">=$(=makedate(year(max(CalMonthYear)), num('7'))) <=$(=Max(CalMonthYear))"}>} Direct_Cost),

if(only({<FiscalFlag=, CalMonthYear=>}FiscalFlag) = 0,

sum (  {< CalMonthYear={">=$(=makedate(year(max(CalMonthYear)-1), num('7'))) <=$(=Max(CalMonthYear))"}>} Direct_Cost)

))

I am flagging the months which are greater than 7 as 1 and less than 7 as 0 and using that FiscalFlag  to calculate.

Thanks,

[tresesco · Accepted solution]

May be like attached sample?

[#7]

Awesome...Thanks It worked. I was making it way too complicated than it required.

Thanks.