Qlik View vs Qlik Sense Km formula

[Rahell39]

Dear All,

I am trying to move some formulas from QlikView to Qlik Sense and I am having issues with a distance calculating formula.

In QlikView it works flawless but in Qlik Sense it doesn't show any data, although the formula is the same:

=num(avg((atan2(sqrt(sqr(cos(delivery_lat*pi()/180)*sin(delivery_lon*pi()/180-pickup_lon*pi()/180))+sqr(cos(pickup_lat*pi()/180)*sin(delivery_lat*pi()/180)-sin(pickup_lat*pi()/180)*cos(delivery_lat*pi()/180)*
cos(delivery_lon*pi()/180-pickup_lon*pi()/180))),sin(pickup_lat*pi()/180)*sin(delivery_lat*pi()/180)+cos(pickup_lat*pi()/180)*cos(delivery_lat*pi()/180)*cos(delivery_lon*pi()/180-pickup_lon*pi()/180))*6371)),'#,##0.00')

Do you have any feedback. I am fairly new to Qlik Sense, maybe I am missing something.

Thx!

[pradosh_thakur]

what is the error are you getting when you paste it into the expression editor.  You can see the field and functions highlighted, May be you are missing a field. 

Share the screenshot of the entire expression editor may be.

-Pradosh

[Rahell39]

There is no error, the function says it is ok. But I get a null sign, '-', in tables or in charts.

[Rahell39]

From what I can see the formula is ok in the Desktop version, and ok when I upload it the cloud, but after the first reload, all the value change to "-". 

[Rahell39]

Update on the matter. I managed to narrow it down. It seams that on the desktop version, the lat and lon fields are identified as numbers, or in the cloud no. So the formula is ok, the issue is the filed. This is strange due to the fact that there are no changes in the data loading scrip. Any ideas?

[ArnadoSandoval]

Hi @Rahell39 

It may help if you share with us a sample of your data, at least 10 records; otherwise, we need to even create a sample data set of your data, guessing those latitude and longitude figures your formulas process! I am trying to give a go here, but without sample data is a bit more complex.

hth

[ArnadoSandoval]

Hi @Rahell39 

I tried using a mockup csv file, shown below, I did my tests with QlikSense Desktop and Qlik Cloud, everything worked fine. 😮; perhaps, if we use some of your data may change the result of my tests

PickupCity	pickup_lat	pickup_lon	DeliveryCity	delivery_lat	delivery_lon	ByRoad
Sydney	-33.865	151.2094	Sydney	-33.865	151.2094	0.00
Sydney	-33.865	151.2094	Melbourne	-37.8136	144.9631	963.00
Sydney	-33.865	151.2094	Brisbane	-27.4678	153.0281	1010.00
Sydney	-33.865	151.2094	Perth	-31.9522	115.8589	4110.00
Sydney	-33.865	151.2094	Adelaide	-34.9289	138.6011	1427.00
Sydney	-33.865	151.2094	Gold Coast	-28.0167	153.4	845.00
Sydney	-33.865	151.2094	Canberra	-35.2931	149.1269	288.00

 hth

[Rahell39 · Accepted solution]

Thank you @ArnadoSandoval but like I said in the last update, there is an issue with the filed. When the data is brought back from the data source the lan and lon fields are not seen as numbers, in the cloud version; in the desktop version all is ok. From what a fried tells me the issue, comes from the cloud server settings. As a partial solution I used the '=num(evaluate(filed))' function to pre load the fields as numbers in order for the initial formula to work. 

[ArnadoSandoval]

Hi @Rahell39 

It worked fine for me on desktop and QS cloud, although I was reading the data from a CSV file, so my issue duplication was not exactly and your data source is unknown to us, and it could introduce data handling differences.

hth

[Rahell39]

Thank you the help, the issue was due to Qlik Cloud Server, which didn't saw the field as number.