topic Re: Showing 18 months on Line Chart in QlikView

Showing 18 months on Line Chart

ZoeM — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community.

I am dumb founded on this request, I need to show 18 months on a line chart. the 12 months for the current year and 6 months for the following year.

Can Someone show me how to incorporate it into a set analysis? Is there an alternative way?

Thanks in advance

Re: Showing 18 months on Line Chart

tmackay2015 — Wed, 20 Jun 2018 20:11:40 GMT

try inserting this as set analysis:

{$<[Order Date] ={">=$(=addmonths(today(),-12)) <=$(=addmonths(today(),6))"}>}

This will do 6 months ahead of today and 12 months behind to day.

if not using a master calendar also consider adding a Year-Month dimension to the script using

Year([Order Date]) & ' - ' &Month([Order Date]) as [Order Year - Month]

Re: Showing 18 months on Line Chart

sunny_talwar — Thu, 21 Jun 2018 01:56:19 GMT

Another possibility

Count({<[Order Date] += {"$(='>=' & Date(Max([Order Date])) & '<=' & Date(AddMonths(Max([Order Date]), 6)))"}>}Item)

Re: Showing 18 months on Line Chart

qlikviewwizard — Thu, 21 Jun 2018 03:05:17 GMT

Hi,

You can try something like this.

COUNT( {$<MonthYear={">=$(=Date(AddMonths(Max(MonthYear),-18),'MMM-YY'))<=$(=Date(Max(MonthYear),'MMM-YY'))"}>} Item )

Data:

Load * ,Year([Order Date]) as Year,Month([Order Date]) as Month,Date(MonthStart([Order Date]),'MMM-YY') as MonthYear;

LOAD Country,

Item,

Cost_$,

Quantity,

[Order Date],

Type

FROM

Data.xls

(biff, embedded labels, table is Sheet1$);

Re: Showing 18 months on Line Chart

ZoeM — Thu, 21 Jun 2018 20:46:18 GMT

Unfortunately it didnt work. I do have a Master Calendar in my data set. I think because I need to modify my expression: The main field is coming through a Date Bridge and has to be designated as to what date it is applicable to, see below:

Count({$<[Date Type]={'VPP Dates'},Reference_Only={'No'}>}Date)

I tried modifying it like this:

Count({<[Date Type] += {"$(='>=' & Date(Max([Date Type])) & '<=' & Date(AddMonths(Max([Date Type])& {'VPP Dates'}, 6)))"}>}Gateways)

because I only need to look at the first six months of the following year. The Start should be January of the selected year and the max should be June of the following year.

Any advice?

Re: Showing 18 months on Line Chart

sunny_talwar — Fri, 22 Jun 2018 02:52:26 GMT

No entirely sure I understand what isn't working. Would you be able to explain in the context of the sample provided. What are you getting and what do you intend to get?