10 Replies Latest reply: Mar 31, 2014 2:56 PM by Gayathri Nishtala

# If condition

The calculation below is not working,The if conditions are not getting satisfied. Please help

=IF(MATCH(FC.DATE_FISCAL_MONTH_NUM_LONG,3,6,9,12)>0 and Type='P',

SUM({<FC.DATE_FISCAL_YEAR_LONG={\$(vCurrentYear)},FC.DATE_FISCAL_MONTH_NUM_LONG={">=\$(vPrevMonth)<=\$(vCurrentMonth)"}>}([Direct Hours]/200)),

SUM({<FC.DATE_FISCAL_YEAR_LONG={\$(vCurrentYear)},FC.DATE_FISCAL_MONTH_NUM_LONG={">=\$(vPrevMonth)<=\$(vCurrentMonth)"}>}([Direct Hours]/160))

)

• ###### Re: If condition

try

=IF(MATCH(FC.DATE_FISCAL_MONTH_NUM_LONG,3,6,9,12)>0 and Type='P',

SUM({<FC.DATE_FISCAL_YEAR_LONG={'\$(vCurrentYear)'},FC.DATE_FISCAL_MONTH_NUM_LONG={">=\$(vPrevMonth)<=\$(vCurrentMonth)"}>}([Direct Hours]/200)),

SUM({<FC.DATE_FISCAL_YEAR_LONG={'\$(vCurrentYear)'},FC.DATE_FISCAL_MONTH_NUM_LONG={">=\$(vPrevMonth)<=\$(vCurrentMonth)"}>}([Direct Hours]/160))

)

• ###### Re: If condition

its giving me the same numbers, The set analysis i made for Year and Quarter are working but for Curr month
Vs  Prev month, this is coming wrong

• ###### Re: If condition

And, what is the value of FC.DATE_FISCAL_MONTH_NUM_LONG at time and point of calculation?  Is it defined?

• ###### Re: If condition

Yes it defined contains number(1,2,3..12)

• ###### Re: If condition

OK, I have to clarify my question...

If you use your expression in a chart where this field is an dimension, the match() function should work.  To make sure, just add it as an additional dimension.  If you use it in any other way, and multiple values are selected (or available) in this field, match returns null.  So "if" condition returns false.

• ###### Re: If condition

So how you want me to modify this one. Please help me.if you want I can share the sample file with you

• ###### Re: If condition

Just upload the file, and I (or anybody else) will take a look.

• ###### Re: If condition

Thanks Micheal,

I have not cleared the cache and was trying different calculations, Once I cleared the cache and re applied the calculation it has worked.

Thanks again

have a good day!

• ###### Re: If condition

What is your variable definition for the above mentioned variables?

• ###### Re: If condition

Current Month:

=Max(IF(FC.DATE_FISCAL_YEAR_LONG=\$(vCurrentYear),FC.DATE_FISCAL_MONTH_NUM_LONG))

PrevMonth:

=if(\$(vCurrentMonth)=1,12,\$(vCurrentMonth) -1)

If the Month is multiple of 3(like 3,6,9,12)  then I need to divide the direct hours by 200 else by 160