topic Re: Set analysis help. in QlikView

Set analysis help.

Mon, 03 Feb 2014 02:41:06 GMT

How can I write this in set analysis.

=Sum(if(ActualAccount<>0,
if([Miscellenious Fee]<>0,
year=, Month=, Date='>=$(=MonthStart(Max(Date), -1))<=$(=MonthEnd(Max(Date), -1))'),FeeAmount)))

I am calculating the previous month feeamount if ActualAccount <> 0 and if Miscellenious Fee <>0.

Thanks.

Re: Set analysis help.

MK_QSL — Mon, 03 Feb 2014 07:16:09 GMT

SUM({$<

ActualAccount -= {0},

[Miscellenious Fee] -= {0},

Year =, Month=,

Date = {">=$(=MonthStart(Max(Date)),-1) <= $(=MonthEnd(Max(Date)),-1)"}

>}FeeAmount)

Re: Set analysis help.

Mon, 03 Feb 2014 07:18:48 GMT

SUM({$< ActualAccount -= {0}, [Miscellenious Fee] -= {0}, Year =, Month=, Date = {">=$(=MonthStart(Max(Date)),-1) <= $(=MonthEnd(Max(Date)),-1)"}>}FeeAmount)

Re: Set analysis help.

tabletuner — Mon, 03 Feb 2014 07:44:18 GMT

Hi Aptha0115, keep in mind that the if statements will include your null values. However, the set analysis will always exclude them because they cannot be selected. To correct the behaviour you should insert statements in your load script like:

if(isnull(ActualAccount),0,ActualAccount) AS ActualAccount,

if(isnull([Miscellenious Fee]),0,[Miscellenious Fee]) AS [Miscellenious Fee]