Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be like this
if(len(trim([Actual finish]))>0,Sum( {<[Days]={">-3<1"}, [Year]={$(=max([Year]))}>} [Count Order]))
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"?
Can you share the sample output data set?
I fixed it, but now I need to see the "Total" over that formula:
@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.