topic issue in QlikView

issue

Thu, 26 Feb 2015 09:59:41 GMT

how to write the same exp in back end

=num(num((num(num(fabs(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, AccountNumber_GL={'3020041001'},P_RYEAR=,Companyode={'1500'}>}AmountInLC_Actual_PNL))/10000000,'#,##0.00')

num(fabs(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, AccountNumber_GL={'5500005002'},P_RYEAR=,Companyode={'1500'}>}AmountInLC_Actual_PNL))/10000000,'#,##0.00'),'#,##0.00'))

(num(num(fabs(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, AccountNumber_GL={'6050005001'},P_RYEAR=,Companyode={'1500'}>}AmountInLC_Actual_PNL))/10000000,'#,##0.00')

num(fabs(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, AccountNumber_GL={'6050005004'},P_RYEAR=,Companyode={'1500'}>}AmountInLC_Actual_PNL))/10000000,'#,##0.00'),'#,##0.00'))

(num(num(fabs(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, AccountNumber_GL={'3020041002'},P_RYEAR=,Companyode={'1500'}>}AmountInLC_Actual_PNL))/10000000,'#,##0.00')

num(fabs(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, AccountNumber_GL={'6050005005'},P_RYEAR=,Companyode={'1500'}>}AmountInLC_Actual_PNL))/10000000,'#,##0.00'),'#,##0.00')),'##.00')

num(fabs(sum({<[Fiscal Year_GJAHR] = {'$(vMaxYear1)'},Year=, P_RYEAR=,Companyode={'1500'},Particulars={'Operating expense'} >}AmountInLC_Actual_PNL)

/10000000),'#,##0.00' ),'#,##0.00' )

Re: issue

Thu, 26 Feb 2015 10:17:43 GMT

how we can convert it into backend without set analyis

=sum({< AccountNumber_GL={'5500005002'}>}AmountInLC_Actual_PNL)/10000000

sum({<AccountNumber_GL={'3020041001'}>}AmountInLC_Actual_PNL)/10000000

Re: issue

jyothish8807 — Thu, 26 Feb 2015 10:32:21 GMT

Hi Shweta,

Try like this:

Load *,

if(AccountNumber_GL=5500005002, sum(AmountInLC_Actual_PNL) as A1,

if(AccountNumber_GL=3020041001, sum(AmountInLC_Actual_PNL) as A2,

(A1/1000000)-(A2/10000000) as A3

resident table;

Hope it works

Regards

Re: issue

CELAMBARASAN — Thu, 26 Feb 2015 10:34:18 GMT

Try this kind of approach

Table1:

Load

Sum(AmountInLC_Actual_PNL) AS 1stPart

Resident TableName Where AccountNumber_GL='5500005002';

Let v1stPart = Peek('1stPart');

Drop Table1;

Table2:

Load

Sum(AmountInLC_Actual_PNL) AS 2ndPart

Resident TableName where AccountNumber_GL='3020041001';

Let v2ndPart = Peek('2ndPart');

Drop Table2;

Let vResult = v1stPart - 2ndPart;