topic Re: How to use if stmt within sum function in set analysis? in QlikView

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

Anonymous — Tue, 25 Mar 2014 03:00:37 GMT

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?

jagan — Tue, 25 Mar 2014 03:15:46 GMT

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?

Anonymous — Tue, 25 Mar 2014 03:23:31 GMT

Perfect!!!

Thanks a lot

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

Anonymous — Tue, 25 Mar 2014 03:27:14 GMT

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?

jagan — Tue, 25 Mar 2014 03:33:22 GMT

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?

Anonymous — Tue, 25 Mar 2014 03:51:59 GMT

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

But really helpful in understanding

Thanks a lot!!!