Not Null values in calculation

[TomBond77]

Hi experts

I need to calculate a formula in QlikSense.  There is a date field "Actual finish". Some records show blank / space values for that field. These values should be ignored in this calculation. But the current formula is not working:

Sum( {<[Days]={">-3<1"}, [Year]={$(=max([Year]))},
len(trim([Actual finish]))>0>} [Count Order])

Any ideas how to fix this issue?

Best Regards

Tom

[arulsettu]

May be like this

if(len(trim([Actual finish]))>0,Sum( {<[Days]={">-3<1"}, [Year]={$(=max([Year]))}>} [Count Order]))

[TomBond77]

many thanks, this works.

Now I only need to exclude those records with no "Actual finish" date from the result set. Can you please give me the code for the column "Actual finish"?

[arulsettu]

Can you share the sample output data set?

[TomBond77]

I fixed it, but now I need to see the "Total" over that formula:

[stevejoyce]

@TomBond77 Use set analysis instead of if statement for this...

Sum( {<[Days]={">-3<1"}, [Year]={$(=max([Year]))}, 

[Actual finish] = {"=len(trim([Actual finish]))>0"}

>} [Count Order])

that will also aggregate up to your Total row.