Weirdest thing I have seen in Qlik Sense to date.

[radmin5253]

So I seem to be experiencing an issue with Qlik Sense as best as I can explain here is what is happening:

My boss wanted to exclude the current month from being displayed on the line charts but wanted the data to show in tables so it could be analyzed. So with the help of Antonio (see this link  Re: Trying to exclude the current month from a line charthttps://community.qlik.com/message/1354188#1354188) we came up with the following formula    Sum({$<Date={'<=$(=Date(MonthEnd(Today(),-1),'M/D/YYYY'))'}>} AvgSessionDuration*Sessions)/3600   and it worked like a champ using an exported copy of the original app in Qlik Sense Desktop on my home computer. What I see is a clean break in the current year for September no value displayed at all for October and no line being drawn from September to October all is great in Qlik land.

When I added the single line of code to the actual app which is running on Qlik Sense Enterprise the entire chart disappears as if the formula is so messed up that it can not even display the chart at all.

All the above formula should do is look at todays date and give me the end of the month for the prior month, there by not showing any data in the chart for the current month. Simple enough and like I said it works exactly as I wanted it to in Qlik Sense Desktop but completely fails in Qlik Sense Enterprise.

I loaded up an instance of Qlik Sense Desktop on my laptop and moved a copy of the original app the same one that is used on the Enterprise version and on my home computer and now I get a third completely different chart issue on this version it shows 0 and a line being drawn from the previous month in the line chart.

I have exported the chart from enterprise and moved it over to both my home computer and laptop and back with no difference being noticed in the displaying each issue remains with the machine as I move this app around.

This app has over 100 additional charts, KPI, and tables in it and every single one of them without fail works and looks the same on all three devices. I can not figure the issue out.

Anyone out there have an idea on what could be causing the issue. Open to any and all suggestions as I have spent 3 days looking into it and can find no solution.

[radmin5253]

All while I see that this has a lot of views but I guess no one has ever experienced Qlik Sense acting differently on three different computers using the exact same app and formulas. To make the formula ,more generic I have dumbed it down so that others might be able to just copy and paste into their application and test.

Sum({$<Date={'<=$(=Date(MonthEnd(Today(),-1),'M/D/YYYY'))'}>} Metric1)

Change metric1 to what ever metric you mighty have in your application and adjust the Date format to whatever your default date format is my application happens to have two different date formats thus the need to format MonthEnd(Today(),-1) best I can tell this should work the load editor does not report any issues with it and unless I am missing some little nuance it should be formatted correctly.

The only other thing you need would be some sample dates covering three or four months to see what it does in your instance of Qlik.

I am lost been working on this and can not tell you how many evolutions of tries I have made all to no avail.

[sunny_talwar]

Can you check with double quotes

Sum({$<Date={"<=$(=Date(MonthEnd(Today(),-1),'M/D/YYYY'))"}>} Metric1)

[radmin5253]

I am positive I tried that in one of my attempts but will verify and make sure, after awhile you can lose track.

[radmin5253]

Sunny nope the double quotes did not work same results on two of the three systems my desktop and laptop can not check the enterprise system until I get back to work.

However another clue emerges while working on testing the double quotes I thought about trying a different chart so I used a bar chart and it displayed correctly. I also tried a pie chart and it also displayed correctly when I filtered on just the current year the current month did not appear.

So I converted back to a line chart and it displayed correctly but only had the single dimension of Month, while my original line chart had two dimensions Month and Year so we could see year over year comparison. When I added this second dimension back in the display reverted back to showing a line from last month to the current month and had the current month sum at zero 0. So it may be a bug in the line chart visual with two dimensions set, using Month as primary and Year as secondary and no alternates.

Thanks for your help so far at least I have another set of test to run on the enterprise version in the morning.

[sunny_talwar]

I guess it will be difficult to say without looking at what you are seeing? Do you think you can share a mocked up application where we can see the issue?

[radmin5253]

Yes I think I can do that let me try and get it under half a GB in size and see if we can post it here

[pradosh_thakur]

CAN you please try this

=Sum({$ < "$(=NUM(Date))"= {'<=$(=NUM(TODAY()-DAY(TODAY())))'}>} Metric1)

If there is any issue due to date format it should nullify it.

regards

Pradosh

[rwunderlich]

It would be interesting to see a screenshot.  I believe if you have a multiple Years, you will see a line for October because it existed in the prior year.

-Rob

[radmin5253]

Rob here is a screen shot

The top chart is what I am trying to get to and I can get there if I use the following hard coded formula:

Sum({$<Date={'<=9/30/2017'}>}AvgSessionDuration*Sessions)/3600

The bottom chart is what I get when I use the following formula which I thought replicates the above:

Sum({$<Date={'<=$(=Date(MonthEnd(Today(),-1),'M/D/YYYY'))'}>} AvgSessionDuration*Sessions)/3600

However this screen capture is from Qlik Sense Desktop on my laptop, if I took this same screen capture from my home computer both charts would look identical. The screen capture below is what I see when I do it from the enterprise version at work.

Notice that the chart disappears all together in the bottom chart. This is what is making this so complicated is that on three different deployments I get three different results makes it hard to develop if things do not translate from device to device. This particular application in its full version as literally a hundred different charts, tables, and filters and they all work and display exactly the same on all three deployments until I add the formula:

Sum({$<Date={'<=$(=Date(MonthEnd(Today(),-1),'M/D/YYYY'))'}>} AvgSessionDuration*Sessions)/3600

to the only metric this chart is displaying