Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

ruchibagaria
New Contributor II

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?

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

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.

5 Replies
MVP
MVP

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.

ruchibagaria
New Contributor II

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

Perfect!!!

Thanks a lot

ruchibagaria
New Contributor II

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?

MVP
MVP

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.

ruchibagaria
New Contributor II

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

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

But really helpful in understanding

Thanks a lot!!!

Community Browser