topic Re: Convert a nested if expression into set analysis in QlikView

Convert a nested if expression into set analysis

Fri, 14 Feb 2014 23:12:04 GMT

Hello

I am trying to convert a nested if expression into a set expression. Any inputs on this would be appreciated.

Here is the expression

If([Currency]='Transactional',

Sum(If( [GL Date]>= $(=[StartPeriod]) AND [GL Date]<= $(=[EndPeriod]) and

([Revenue Type]='DEF' or [Revenue Type]='CLIB') AND [Forecast Flag]='N' and (IsNull([Schedules ATTRIBUTE1]) or [Schedules ATTRIBUTE1] = 'CM') and [Schedule On Hold]='N' and ATTRIBUTE44<>'OPEN_BAL_CONV',[Net Amount])),

if([Currency]='Functional',

Sum(If( [GL Date]>= $(=[StartPeriod]) AND [GL Date]<= $(=[EndPeriod]) and

([Revenue Type]='DEF' or [Revenue Type]='CLIB') and [Forecast Flag]='N' and (IsNull([Schedules ATTRIBUTE1]) or [Schedules ATTRIBUTE1] = 'CM') and [Schedule On Hold]='N' and ATTRIBUTE44<>'OPEN_BAL_CONV',[Net Amount Operating Currency])),

if([Currency]='Reporting',

Sum(If( [GL Date]>= $(=[StartPeriod]) AND [GL Date]<= $(=[EndPeriod]) and

// Sum (If(([GL Date]>= $(=[StartPeriod]) AND [GL Date]<= $(=[EndPeriod]))and [Initial Entry Flag]='Y',[Net Amount],0))

)

Thanks

Re: Convert a nested if expression into set analysis

rwunderlich — Fri, 14 Feb 2014 23:52:16 GMT

For starters, it looks like the only difference between the if() is which field gets SUMmed? If so, it can be simplified to:

Sum(If( [GL Date]>= $(=[StartPeriod]) AND [GL Date]<= $(=[EndPeriod]) and

,alt(pick(Match([Currency], 'Transactional', 'Functional', 'Reporting'),[Net Amount], [Net Amount Operating Currency], [Net Amount Reporting Currency]),0)

,0)

)

If you can confirm that works, I'll help you convert it a set expression.

-Rob

Re: Convert a nested if expression into set analysis

Sat, 15 Feb 2014 00:08:40 GMT

Hello Rob

This expression is working perfectly.

How do I convert this to set analysis now?

Thanks

Re: Convert a nested if expression into set analysis

Sat, 15 Feb 2014 01:51:25 GMT

if(sum({<[GL Date]={'>=$(=[StartPeriod])<=$(=[EndPeriod])'},[Revenue Type]={'DEF','CLIB'},[Forecast Flag]={'N'},[Schedules ATTRIBUTE1]={Null(),'CM'},[Schedule On Hold]={'N'},Attribute={"*"}-{'OPEN_BAL_CONV'},[Currency]={'[Currency]'}>},[Net Amount],if(sum({<[GL Date]={'>=$(=[StartPeriod])<=$(=[EndPeriod])'},[Revenue Type]={'DEF','CLIB'},[Forecast Flag]={'N'},[Schedules ATTRIBUTE1]={Null(),'CM'},[Schedule On Hold]={'N'},Attribute={"*"}-{'OPEN_BAL_CONV'},[Currency]={'Transactional',[Net Amount Operating Currency],sum({<[GL Date]={'>=$(=[StartPeriod])<=$(=[EndPeriod])'}>},[Revenue Type]={'DEF','CLIB'},[Forecast Flag]={'N'},[Schedules ATTRIBUTE1]={Null(),'CM'},[Schedule On Hold]={'N'},Attribute={"*"}-{'OPEN_BAL_CONV'},[Currency]={'Reporting'}>}[Net Amount Reporting Currency],0)

Re: Re: Convert a nested if expression into set analysis

rwunderlich — Sat, 15 Feb 2014 22:45:27 GMT

After simplifying with the pick(match)), I'm not sure I would convert this to SA unless I had to. I think it reads easier as an if. It's somewhat complicated by selection of nulls and another value in [Schedules ATTRIBUTE1]. Can't tell if I have the syntax right without data, but here goes.

=sum(

{

($-<[Schedules ATTRIBUTE1]={*}> + <[Schedules ATTRIBUTE1]={'CM'}>)

[GL Date]={">=$(=[StartPeriod])<=$(=[EndPeriod])"}

,[Revenue Type]={'DEF','CLIB'}

,[Forecast Flag]={'N'}

,[Schedule On Hold]={'N'}

,[ATTRIBUTE44]=-{'OPEN_BAL_CONV'}

}

alt(

pick(Match([Currency], 'Transactional', 'Functional', 'Reporting')

,[Net Amount], [Net Amount Operating Currency], [Net Amount Reporting Currency])

,0)

)

-Rob

http://masterssummit.com

Re: Re: Convert a nested if expression into set analysis

Tue, 18 Feb 2014 18:06:33 GMT

Hello Rob

Can you please advise me on how to simplify the following expression further or write it in a more efficient way

sum(aggr(If([Currency]='Transactional',$(vf-ContingencyPersuasiveEvidence),

([Currency]='Functional',

$(vf-ContingencyPersuasiveEvidence) * max(if([Revenue Schedule Type]='RPRO-REVENUE',

[EX Rate]),

([Currency]='Reporting',

($(vf-ContingencyPersuasiveEvidence) * max(if([Revenue Schedule Type]='RPRO-REVENUE',[EX Rate]),0)) *

max(if([Revenue Schedule Type]='RPRO-REVENUE',[Reporting Currency Ex Rate]),0)

)

,[Transaction ID]))

Thanks

Re: Convert a nested if expression into set analysis

rwunderlich — Wed, 19 Feb 2014 00:32:11 GMT

It doesn't look half-bad as it is. Are you having trouble with it?

-Rob

Re: Convert a nested if expression into set analysis

Wed, 19 Feb 2014 00:53:22 GMT

Hello Rob

No the above one I was able to convert to set.

I am having issue with the following expression

sum(aggr(If([Currency]='Transactional' and [Dummy1]='Y' ,[Price List],

([Currency]='Functional' and [Dummy1]='Y',

[Price List] * max(if([Revenue Schedule Type]='RPRO-COGS',

[EX Rate]),

([Currency]='Reporting' and [Dummy1]='Y',

([Price List] * max(if([Revenue Schedule Type]='RPRO-COGS',[EX Rate]),0)) *

max(if([Revenue Schedule Type]='RPRO-COGS',[Reporting Currency Ex Rate]),0)

)

,[Transaction ID]))

Is there a way to simplify it?