Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Rahell39
Contributor III
Contributor III

Qlik View vs Qlik Sense Km formula

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!

Labels (3)
1 Solution

Accepted Solutions
Rahell39
Contributor III
Contributor III
Author

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. 

View solution in original post

9 Replies
pradosh_thakur
Master II
Master II

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

Learning never stops.
Rahell39
Contributor III
Contributor III
Author

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

Rahell39
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Specialist II
Specialist II

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

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
ArnadoSandoval
Specialist II
Specialist II

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

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Rahell39
Contributor III
Contributor III
Author

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
Specialist II
Specialist II

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

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Rahell39
Contributor III
Contributor III
Author

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