5 Replies Latest reply: Mar 24, 2014 11:52 PM by Ruchi Bagaria

# How to use if stmt within sum function in set analysis?

I need the foll:

 Fyear GROUP_DESC Amount FY13 Net sales 2343 Profit 12% FY12 Net sales 3452 Profit 23%

I am using Set analysis something like foll which is having four diff expressions and giving me four columns in the table:

sum({<GROUP_DESC={'Net sales'},FYear={\$(vPrevFY)}>}Amount)/1000000

sum({<GROUP_DESC={'Net sales'},FYear={\$(vCurrFY)}>}Amount)/1000000

sum({<GROUP_DESC={'Profit'},FYear={\$(vPrevFY)}>}Amount)/1000000

sum({<GROUP_DESC={'Profit'},FYear={\$(vCurrFY)}>}Amount)/1000000

how can i get all these four in one condition so that i get only one column using if stmt?

• ###### Re: How to use if stmt within sum function in set analysis?

Hi,

Try this

=sum({<GROUP_DESC={'Net sales', 'Profit'},FYear={\$(vPrevFY), \$(vCurrFY)}>}Amount)/1000000

Regards,

jagan.

• ###### Re: How to use if stmt within sum function in set analysis?

The only issue is that my Profit is not showing as %. How do I do that?

• ###### Re: How to use if stmt within sum function in set analysis?

Hi,

Can you attach the sample file, so that it would be easier to understand.  If you have a dimension GROUP_DESC then try like this

=If(GROUP_DESC  = 'Profit', Num(sum({<GROUP_DESC={'Net sales', 'Profit'},FYear={\$(vPrevFY), \$(vCurrFY)}>}Amount)/1000000, '##0%'),  Num(sum({<GROUP_DESC={'Net sales', 'Profit'},FYear={\$(vPrevFY), \$(vCurrFY)}>}Amount)/1000000, '#,##0'))

Hope this helps you.

Regards,

Jagan.

• ###### Re: How to use if stmt within sum function in set analysis?

It does work for the Current year.. but not for previous year..