Simple count results do not match

[Nerimera]

Ciao Community,

I have tried several ideas from other question on my own problem but cant get the result right.

I want to count the number of orderlines that show unaligned delivery dates.

- when field Delivery Date Difference is >0

- when field Delivery Date Difference is <0

- sum of both (65 is correct)

Delivery Dat Difference = 0 is excluded from filter, all calculation formulas are done in data loading.

I wonder if I made a mistake in my calculation formula during Load because when I send my data to excel all negative numbers only show ## and the positive ones dont seem to show the number of days, but time format?

There must be something wrong in my understanding of how to manage the data.

Can somebody help?

Thank you very much & kind regards

Neri

[dplr-rn]

it seems as  the DelDiffLogSal seems to have some issue. as you want just days

try replace it will just SalesDeliver-LogDelivery as DelDiffLogSale,

if those columns have time also i would floor them and the do the subtraction

[Chanty4u]

can  you try this?

count( {$<Del = {“<0”}>} distinct Orderline)

or

count( {$<Del = {“>0”}>} distinct Orderline)

[Nerimera]

Ciao,

thanks for you suggestion.

I got the same result which first confused me but actually helped me understand that I just lacked understanding of my data. I got now how to do the count.

[Nerimera]

Hi,

thanks. I used the floor function for all dates. However, in excel this hasnt brought on any change.

Negative number show ### wih the correct number in the formula bar and positive numbers shows as time.

Did I use it correctly?

[marcus_sommer]

Excel is with the standard-settings not capable of handling negative times. Usually it's not recommended to change this setting because it's a global user-setting - means it will apply within all excel-files and it has further side-effects which you should be aware of.

- Marcus

[Brett_Bleess]

So using the Fabs() function may do the trick if you do not need the negative?  Just wanted to toss this out there:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/GeneralNu...

Regards,
Brett