topic Re: Set Analysis to Match Dimension in QlikView

Set Analysis to Match Dimension

Mon, 12 Dec 2011 19:07:38 GMT

Hi Everyone,

I am struggling with creating a Expression for a SUM that will match my selection for my dimension

Dimension:

=IF(AccountAlias.FlagCommitmentFee = 1 OR AccountAlias.FlagAmendmentFee = 1 OR AccountAlias.FlagExtensionFee = 1 OR AccountAlias.FlagRenewalFee = 1,FGL.NativeLoanKey,Null())

Expression:

=If(Dimensionality ( ) > 8,

SUM({$<[FGLL.GLPostDate] = {">=$(vRollbackEffectiveDate)<=$(=DATE(FLS_FullDateNum))"}, AccountAlias.FlagCommitmentFee = {"1"}>} AGGR(FactGLL.TransactionAmount,FGLL.NativeLoanKey,AccountAlias.Fees,FGLL.GLPostDate,Investor_SLF)) * (-1)

)

Basically I wouldl like to know how to do ORs in the Set Anaylsis syntax and can't quite figure it out.

Set Analysis to Match Dimension

Mon, 12 Dec 2011 19:39:53 GMT

What you could do is create a new field on the script:

if(AccountAlias.FlagExtensionFee = 1 OR AccountAlias.FlagRenewalFee = 1,1,0) as OrFlag

Then you can do:

OrFlag={"1"}

Re: Set Analysis to Match Dimension

Mon, 12 Dec 2011 19:56:31 GMT

I sort of have what I need... Just not sure how to combine them.

SUM({$<[FGLL.GLPostDate] = {">=$(vRollbackEffectiveDate)<=$(=DATE(FLS_FullDateNum))"} >} AGGR(FactGLL.TransactionAmount,FGLL.NativeLoanKey,AccountAlias.Fees,FGLL.GLPostDate,Investor_SLF)) * (-1)

and

SUM({<AccountAlias.FlagCommitmentFee = {"1"}> + <AccountAlias.FlagAmendmentFee = {"1"}>} AGGR(FactGLL.TransactionAmount,FGLL.NativeLoanKey,AccountAlias.Fees,FGLL.GLPostDate,Investor_SLF)) * (-1)

Set Analysis to Match Dimension

Mon, 12 Dec 2011 20:00:31 GMT

Alright, do the fields

AccountAlias.FlagCommitmentFee and AccountAlias.FlagAmendmentFee come from the same table? If they do then creating a new field if either equals 1 should allow you to do this:

SUM({$<[FGLL.GLPostDate] = {">=$(vRollbackEffectiveDate)<=$(=DATE(FLS_FullDateNum))"}, OrFlag={"1"}>} AGGR(FactGLL.TransactionAmount,FGLL.NativeLoanKey,AccountAlias.Fees,FGLL.GLPostDate,Investor_SLF)) * (-1)

Re: Set Analysis to Match Dimension

Mon, 12 Dec 2011 20:05:09 GMT

I can't just do that at the moment. I do not have the "Load Script to work with". I only have the QVW that is generated from the QVD's

Re: Set Analysis to Match Dimension

Mon, 12 Dec 2011 20:07:12 GMT

Oh alright, I haven't had the chance to work with QVD's, but Would something like this work?

Load *,

if(AccountAlias.FlagExtensionFee = 1 OR AccountAlias.FlagRenewalFee = 1,1,0) as OrFlag;

Select *

From .......QVD