Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
bmchale1983
Contributor III
Contributor III

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.

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

Capture.PNG

View solution in original post

9 Replies
vishsaggi
Champion III
Champion III

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),'$')

tripatirao
Creator II
Creator II

Hi,

Try like this

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



bmchale1983
Contributor III
Contributor III
Author

Capture.PNG

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:
LOAD Lead_Number,
Lead_Create_Date,
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_Received_Date,
Appraisal_2_Recieved_Date,
Scheduled_Closing_Date,
Closing_Package_Received_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,
Day_Count_Lead_Reg,
Lead_Count,
Loan_Count,
Extract_Date,
Num_Days_Lock_Expiration,
Loan_Program_Code,
Acct_Sold_Status,
Foreign_National_Indicator,
Self_Sourced,
Lead_Status_Code,
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:
LOAD  Lead_Number,Lead_Create_Date as CanonicalDate, 'Leads' as DateType
Resident Temp_Actuals;

Concatenate(Temp_DateBridge)
LOAD Lead_Number,Registration_Date as CanonicalDate, 'Registrations' as DateType
Resident  Temp_Actuals;
Concatenate(Temp_DateBridge)
LOAD Lead_Number,Closing_Date as CanonicalDate, 'Fundings' as DateType
Resident  Temp_Actuals;



Temp2_Actuals:
NoConcatenate
Load * resident  Temp_Actuals;
join  Load * resident Temp_DateBridge;

drop table Temp_Actuals;
drop table Temp_DateBridge;


Actuals:
NoConcatenate Load *,
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;

sunny_talwar

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?

sunny_talwar

Are you excluding null canonical date of cancellation date?

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

bmchale1983
Contributor III
Contributor III
Author

Capture.PNG

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

bmchale1983
Contributor III
Contributor III
Author

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

sunny_talwar

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

Capture.PNG

Capture.PNG

bmchale1983
Contributor III
Contributor III
Author

oh my goodness, thank you