topic Re: Simple count results do not match in QlikView

Simple count results do not match

Nerimera — Fri, 15 Nov 2024 23:44:25 GMT

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

Re: Simple count results do not match

dplr-rn — Mon, 26 Oct 2020 14:41:58 GMT

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

Re: Simple count results do not match

Chanty4u — Tue, 27 Oct 2020 07:43:15 GMT

can you try this?

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

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

Re: Simple count results do not match

Nerimera — Tue, 27 Oct 2020 09:59:50 GMT

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.

Re: Simple count results do not match

Nerimera — Tue, 27 Oct 2020 10:03:47 GMT

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?

Re: Simple count results do not match

marcus_sommer — Tue, 27 Oct 2020 12:32:40 GMT

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

Re: Simple count results do not match

Brett_Bleess — Wed, 04 Nov 2020 21:22:45 GMT

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/GeneralNumericFunctions/fabs.htm

Regards,
Brett