Expressions - Ignore rows with missing field values

[#1]

Hi everyone,

I have a simple expression to calculate product age: (Year - [First Year Delivery]). How do I tell the expression to ignore rows where [First Year Delivery] is blank? Those rows are creating results that distort my later calculations of averages.

[MarcoWedel]

in the script:

Where not IsNull([First Year Delivery])

or

Where Len([First Year Delivery]) > 0

or

Where [First Year Delivery] <> ''

[#3]

Hi Marco,

Would you mind showing me how to insert in script? I have created buckets for my data with the following - where would I add the WHERE qualifier?

if([First Year Delivery]<0,0,

    if((FiscalYear-[First Year Delivery])<0,0,

    if((FiscalYear-[First Year Delivery])>0 and (FiscalYear-[First Year Delivery])<10,'0-9',

    if((FiscalYear-[First Year Delivery])>=10 and (FiscalYear-[First Year Delivery])<20,'10-19',

    if((FiscalYear-[First Year Delivery])>=20 and (FiscalYear-[First Year Delivery])<30,'20-29',

    if((FiscalYear-[First Year Delivery])>=30,'30+',

    )))))) as [Age Bucket]

[MarcoWedel · Accepted solution]

Hi,

something like

LOAD FiscalYear,

          [First Year Delivery],

          if([First Year Delivery]<0,0,

             if((FiscalYear-[First Year Delivery])<0,0,

               if((FiscalYear-[First Year Delivery])>0 and (FiscalYear-[First Year Delivery])<10,'0-9',

                if((FiscalYear-[First Year Delivery])>=10 and (FiscalYear-[First Year Delivery])<20,'10-19',

                 if((FiscalYear-[First Year Delivery])>=20 and (FiscalYear-[First Year Delivery])<30,'20-29',

                  if((FiscalYear-[First Year Delivery])>=30,'30+',

          )))))) as [Age Bucket]

From YourSource

Where not IsNull([First Year Delivery]);

[#5]

Perfect, thanks Marco!