Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Need help with the Expression if condition

Hi All,

I’m trying to calculate the number of Days an Order took from BDate to CDate based on the “Date” field. If the” Date” is blank then need to ignore that Order. The below expression is calculating the days correctly for most cases but when the Order has either only BDate or CDate it’s not calculating it correctly.  There should be value for both BDate and CDate, if either BDate or CDate is blank then need to ignore that order.

For example in the attached qvw and excel Order PTC has only CDate, BDate is blank so this order should be ignored.  I tried to write the following expression but it’s not working. Please can someone help me with the expression?  Thanks,

Ramya

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Just add the null condition at the start of your IF condition like the below

Max(Aggr(

   

    if( isnull(min(BDate))=0 and isnull(min(CDate))=0 ,

    IF(InQuarter(min(CDate), Quarter_Start, 0) AND InQuarter(min([BDate]), Quarter_Start, 0), min(CDate) - min([BDate]),

IF (InQuarter(min(CDate), Quarter_Start, 0), min(CDate) - Quarter_Start,

IF (InQuarter(min([BDate]), Quarter_Start, 0), DayStart(Quarter_End) - min([BDate]),

IF (not(InQuarter(min(CDate), Quarter_Start, 0)) and not(InQuarter(min([BDate]), Quarter_Start, 0)) and min(CDate) > Quarter_Start and min([BDate]) < Quarter_End, Quarter_End - Quarter_Start

))))), Year, Order))

View solution in original post

2 Replies
Highlighted
Specialist II
Specialist II

Just add the null condition at the start of your IF condition like the below

Max(Aggr(

   

    if( isnull(min(BDate))=0 and isnull(min(CDate))=0 ,

    IF(InQuarter(min(CDate), Quarter_Start, 0) AND InQuarter(min([BDate]), Quarter_Start, 0), min(CDate) - min([BDate]),

IF (InQuarter(min(CDate), Quarter_Start, 0), min(CDate) - Quarter_Start,

IF (InQuarter(min([BDate]), Quarter_Start, 0), DayStart(Quarter_End) - min([BDate]),

IF (not(InQuarter(min(CDate), Quarter_Start, 0)) and not(InQuarter(min([BDate]), Quarter_Start, 0)) and min(CDate) > Quarter_Start and min([BDate]) < Quarter_End, Quarter_End - Quarter_Start

))))), Year, Order))

View solution in original post

Highlighted
Creator II
Creator II

Thank you so much Susant.

Ramya