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

[Anonymous]

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?

[jagan · Accepted solution]

Hi,

Try this

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

Regards,

jagan.

[Anonymous]

Perfect!!!

Thanks a lot

[Anonymous]

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

[jagan]

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.

[Anonymous]

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

But really helpful in understanding

Thanks a lot!!!