9 Replies Latest reply: Jun 19, 2017 11:44 AM by Brien McHale

Set Analysis Not Equal to 0

I have the below expression, but not coming with any results and feel it has something to do with 'not equal'.

This works:

Num(sum({<[DateType] = {"Fundings"} >} Loan_Amount),'\$')

This doesn't:

Num(sum({<[DateType] = {"Fundings"}, Cancellation_Date -={'0'} >} Loan_Amount),'\$')

and I need to exclude Amounts when Canonical date is null or is equal to 0, it appears to be =0 in the data.

Any help would be appreciated, thanks.

• Re: Set Analysis Not Equal to 0

Try this may be:

Num(sum({<[DateType] = {"Fundings"} > +  < Cancellation_Date -= {'0'}  >} Loan_Amount),'\$')

OR

Not sure about below, you can try?

Num(sum({<[DateType] = {"Fundings"} > +  < Cancellation_Date -= {"= \$(= Len(Trim(Cancellation_Date)) = 0)"}  >} Loan_Amount),'\$')

• Re: Set Analysis Not Equal to 0

This one accepted, but still not excluding and apologies I meant:  Num(sum({<[DateType] = {"Fundings"} > + < CanonicalDate -= {'0'} >} Loan_Amount),'\$')

My datetype is essentially looking at lead date, registration date, and close date to build canonical date and then I concatenated this into actuals table and am trying to see actual fundings only if canoncical date has been populated with actual date.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='\$#,##0.00;(\$#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Temp_Actuals:
Application_Date,
Loan_Amount,
Registration_Date,
Closing_Date,
Cancellation_Date,
Denial_Date,
Disbursement_Occurred_Date,
Property_City,
Property_State,
Property_Zip_Code,
Loan_Officer_Name,
PHH_Loan_Officer_ID,
Employee_Active_Indicator,
Loan_Officer_ID,
Sales_Manager,
Referring_Employee,
Referring_Employee_ID,
Branch_Number,
Branch_Name
Purchase_Or_Refi,
Loan_Purpose_Code,
Loan_Status,
UW_Status,
First_Approval_Date,
Approved_Date,
PHH_STAT_CDE,
Underwriting_Submission_Date,
Discount_Program,
Program_Number,
Program_Description,
Lock_Expiration_Date,
Processor_Name,
Assistance_Provider_1,
Assistance_Amount_1,
Assistance_Provider_2,
Assistance_Amount_2,
Appraisal_2_Recieved_Date,
Scheduled_Closing_Date,
Note_Recieved_Date,
Appraisal_Inspection_Date,
Appraisal_Confirmed_Date,
Credit_Contingency_Date,
Closed_Ind,
Registration_Ind,
Cancelled_Ind,
Referral_Ind,
Lock_Expiration_Status,
Product_Category,
Product_Segmentation,
Private_Banking_Ind,
Day_Count_Reg_Closing,
Loan_Count,
Extract_Date,
Num_Days_Lock_Expiration,
Loan_Program_Code,
Acct_Sold_Status,
Foreign_National_Indicator,
Self_Sourced,
APPLICATION_STATUS,
Level_Product_Code_2,
Level_Product_Code_3,
Program_Type,
LOCK_DT_TM,
Loan_ID,
Specialized_Sales_Manager,
Sales_Area,
Mortgage_Consultant,
Sales_ID,
Employee_ID,
if(Region='MORTGAGE - CRA', 'CRA',
if(Region='NORTHERN CALIFORNIA AREA' , 'NORTHERN CALIFORNIA EAST & WA MKT',
if(Region='PRIVATE BANKING' , 'PB', Region
)))
as Region

FROM
[\\qlikviewnau.us.hsbc\Content\US RBWM\Sources\AOP Source\AOP_ExtractvQlik.xlsx]
(
ooxml, embedded labels, table is Sheet1);

Temp_DateBridge:
Resident Temp_Actuals;

Concatenate(Temp_DateBridge)
Resident  Temp_Actuals;
Concatenate(Temp_DateBridge)
Resident  Temp_Actuals;

Temp2_Actuals:
NoConcatenate

drop table Temp_Actuals;
drop table Temp_DateBridge;

Actuals:
Region&'-'&Product_Category&'-'&DateType&'-'&month(CanonicalDate)&'-'&Year(CanonicalDate) as AreaTargetKey,
Mortgage_Consultant&'-'&Sales_ID&'-'&Employee_ID&'-'&DateType&'-'&month(CanonicalDate)&'-'&Year(CanonicalDate) as LOTargetKey
resident Temp2_Actuals;

drop table Temp2_Actuals;

• Re: Set Analysis Not Equal to 0

Are you excluding null canonical date of cancellation date?

Also, what is the dimension of the chart where you are using this expression?

• Re: Set Analysis Not Equal to 0

Hi,

Try like this

Num(sum({<[DateType] = {"Fundings"}, Cancellation_Date -={} >} Loan_Amount),'\$')

• Re: Set Analysis Not Equal to 0

If it is =0 in the data... this should work

Sum({<[DateType] = {"Fundings"}, Cancellation_Date -={'0'} >} Loan_Amount)

I wonder why it doesn't. Would you be able to share a sample where this isn't working?

• Re: Set Analysis Not Equal to 0

The dimension is from a master calendar I created for canonical date, and is MonthCAD(Month Canonical Date)

• Re: Set Analysis Not Equal to 0

If I filter it by year, the blank at the end is removed, but I am hoping to not have to do that

• Re: Set Analysis Not Equal to 0

Why don't you check 'Suppress When Value Is Null' on the dimension tab to hide the '-' in your dimension

• Re: Set Analysis Not Equal to 0

oh my goodness, thank you