topic Re: Simplify the below expression in QlikView

Simplify the below expression

Tue, 18 Feb 2014 18:02:15 GMT

Hello

Can someone 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: Simplify the below expression

Tue, 18 Feb 2014 18:27:24 GMT

Hi,

Try with below:

sum(aggr(pick(match([Currency],'Transactional','Functional','Reporting') ,$(vf-ContingencyPersuasiveEvidence),

$(vf-ContingencyPersuasiveEvidence) * max({<[Revenue Schedule Type]= {'RPRO-REVENUE'}>} [EX Rate]),

$(vf-ContingencyPersuasiveEvidence) * max({<[Revenue Schedule Type]= {'RPRO-REVENUE'}>} [EX Rate]) *

max({<[Revenue Schedule Type]= {'RPRO-REVENUE'}>} [Reporting Currency Ex Rate]) ),[Transaction ID]))

else, use another 2 variables to store Max(Ex-Rate) and max(Reporting currency ex rate) and then try to replace them in above expression.

Re: Simplify the below expression

Tue, 18 Feb 2014 19:15:20 GMT

Hello Venu

This works fine. Thanks a lot. But if I have null or not is null condition how would I put that into set or more simplified manner.

For example

How would you simplify the below expression

sum(aggr(If([Currency]='Transactional' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)) ,[Price List],

([Currency]='Functional' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)),

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

[EX Rate]),

([Currency]='Reporting' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)),

([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]))

Re: Simplify the below expression

Tue, 18 Feb 2014 21:00:16 GMT

Hello venugopal4s

How would you simplify the below expression or convert it into set analysis?

([Currency]='Functional' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)),

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

[EX Rate]),

([Currency]='Reporting' and (not isnull(ATTRIBUTE48) OR not isnull(ATTRIBUTE49) OR not isnull(ATTRIBUTE50) OR not isnull(ATTRIBUTE51) OR not isnull(ATTRIBUTE52) OR not isnull(ATTRIBUTE36)),

([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]))

Re: Simplify the below expression

Wed, 19 Feb 2014 05:08:24 GMT

Hi,

I tried my level best. See the below expression whether it can help you or not.

create a variable and assign the expression.

vPriceList = Only({<ATTRIBUTE48 -= {'$(=Null())'}, ATTRIBUTE49 -= {'$(=Null())'},

ATTRIBUTE50 -= {'$(=Null())'}, ATTRIBUTE51 -= {'$(=Null())'},

ATTRIBUTE52 -= {'$(=Null())'}, ATTRIBUTE36 -= {'$(=Null())'} >} [Price List]);

Use the variable in your expression.

sum(aggr(pick(match([Currency],'Transactional','Functional','Reporting') , vPriceList,

vPriceList * max({<[Revenue Schedule Type]= {'RPRO-COGS'}>} [EX Rate]),

vPriceList * max({<[Revenue Schedule Type]= {'RPRO-COGS'}>} [EX Rate]) *

max({<[Revenue Schedule Type]= {'RPRO-COGS'}>} [Reporting Currency Ex Rate]) ),[Transaction ID]))

Re: Simplify the below expression

Wed, 19 Feb 2014 07:02:11 GMT

Hello Venu

I have included the null conditions in the bac end of my script as a dummy dimension.

Now my expression looks like this

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]))

Can we simplify this as set analysis?