Set Analysis Not Equal to 0

[bmchale1983]

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.

[vishsaggi]

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]

Hi,

Try like this

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

[bmchale1983]

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]

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

[bmchale1983]

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 · Accepted solution]

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

[bmchale1983]

oh my goodness, thank you