Last 18 months report issues QlikSense Desktop vs QlikSense Server

[#1]

Back in April when my company was considering going with QlikSense, I downloaded QlikSense Desktop (version 2.2) to play around with and figure out viability. Back in late May/June, my company installed QlikSense Enterprise in house (version 2.0.9). At times I have developed on the server in my local work area. At other times, I have played around with things in my local Desktop copy before migrating my work up to the server.

I recently came across something that I am struggling with, that I appear to have gotten working in my Desktop environment, but cannot get working on the Enterprise Server environment.

I was working on a "last 18 month report (shows current month and the previous 17 months)" with sales by revenue and sales by units. I have worked on both bar charts and pivot tables for the business. My dimensions are MonthYear (figured out in my master calendar) and ProductSubType (a refinement of our two main product lines). My measure is either Sales (ie, revenue) or Units (ie, units sold). We are basing both off of InvoiceDate as that denotes an actual sale in our case. I will give an example of the sales (revenue) set analysis I am using. DealerCostUSD is the value I am aggregating on.

Here is my set analysis filter for the measure:

SUM({$<Month=,Year=,InvoiceDate={">=$(=MonthStart(AddMonths(Max(InvoiceDate),-17)))<$(=MonthEnd(Max(InvoiceDate)))"}>} DealerCostUSD)

Another note before I go further....I have hooked my local QlikSense Desktop up to pull from backed up copies of production data. QlikSense Server pulls from the live production databases. In either case I am viewing the same data.

To continue, when I use that above, I get a populated bar chart (or pivot table). I see values for the last 18 months. When I use the same on the QlikSense server, I get nothing. No data. Nothing. No bars. No data.

I am not sure what is wrong? Since we are using the "upgrade track" for the server (ie, 2.0.9) and the desktop versions follow the "feature track" (ie, 2.2) is this a bug that is fixed in my local desktop or a feature not yet implemented unless we move our server to version 3.0? Do I have something wrong with my set analsyis (then why would it work in Desktop and not server???) Is there a workaround I could use? Thoughts?

[#2]

Just an FYI I did try out the following

SUM({$<InvoiceDate={"<$(Today())"}>} DealerCostUSD)

and my bar chart now shows data. This does not meet my requirements of trying to get current month and prior 17 months. But it shows I can pull data. Still trying to figure out what is wrong.....

[#3]

I also notice that I get data on QlikServer if I take the equals out from the beginning of the dollar expansion function calls. ie,

SUM({$<Month=,Year=,InvoiceDate={">=$(MonthStart(AddMonths(Max(InvoiceDate),-17)))<$(MonthEnd(Max(InvoiceDate)))"}>} DealerCostUSD)

But then the problem is I get all data rather than just the last 18 months. And again, the same formula but with the equals sign

SUM({$<Month=,Year=,InvoiceDate={">=$(=MonthStart(AddMonths(Max(InvoiceDate),-17)))<$(=MonthEnd(Max(InvoiceDate)))"}>} DealerCostUSD)

works just fine in my Desktop environment and pulls in the last 18 months of data.

I just don't get it. This is really starting to warp my brain.

Anybody have any workarounds to suggest?

[#4 · Accepted solution]

Sadly, I got nothing on this.....

So I came up with a workaround. I calculated being within the last 18 months (ie, current month plus 17 prior) within my master calendar at load time.

If(Num(TempDate) > Num(Today()), 'N', If(Num(TempDate) >= Num(AddMonths(MonthStart(Today()),-17)),'Y','N')) As IsInLast18Months

And then my bar charts and pivot tables I can easily filter on it now using set analysis:

SUM({$<IsInLast18Months={'Y'}>} DealerCostUSD).

Works like a charm in both my QlikSense Desktop 2.2 and my QlikSense Server Enterprise 2.0.9.