- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
There is no error, the function says it is ok. But I get a null sign, '-', in tables or in charts.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 "-".
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
A journey of a thousand miles begins with a single step.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
A journey of a thousand miles begins with a single step.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
A journey of a thousand miles begins with a single step.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you the help, the issue was due to Qlik Cloud Server, which didn't saw the field as number.