topic Re: Creating a accumulated graph in QlikView

Creating a accumulated graph

Thu, 15 Sep 2016 08:51:27 GMT

Hi,

I am new to this and I need some help understanding how to work with dates and how to use it as a dimension in a graph.

Lets say I have this data:

Product Eff_date

A 2015-12-12

B 2016-01-01

C 2016-02-02

D 2016-03-03

E 2016-05-05

I want to create a accumulated graph and do a count of how many products I have for each month starting on January until present month (in this case May).

It would display these numbers:

January: 2

February: 3

Mars: 4

April: 4

May: 5

The difficulties I have is that I do not know how to do it when I have no new products in April so I can’t use Eff_date as a month dimension. I only need an overview for all the months so I do not feel the need to create some sort of calendar?

Any suggestions would be helpful

Re: Creating a accumulated graph

krishnacbe — Thu, 15 Sep 2016 15:52:17 GMT

Hi,

Create a master calendar and link that to the Eff. Date

Re: Creating a accumulated graph

sunny_talwar — Thu, 15 Sep 2016 17:53:59 GMT

Try this (The As-Of Table‌):

Table:

LOAD *,

MonthName(Eff_date) as MonthYear;

LOAD * INLINE [

Product, Eff_date

A, 2015-12-12

B, 2016-01-01

C, 2016-02-02

D, 2016-03-03

E, 2016-05-05

];

Max:

LOAD Min(MonthYear) as Min

Resident Table;

LET vMin = Peek('Min');

DROP Table Max;

LinkTable:

LOAD MonthYear as ReportMonthYear,

MonthName(AddMonths(MonthYear, (-IterNo() + 1))) as MonthYear

Resident Table

While AddMonths(MonthYear, (-IterNo() + 1)) >= $(vMin);

Re: Creating a accumulated graph

Fri, 16 Sep 2016 08:20:19 GMT

Great but then I loose April as a month. I need to have all months from January until present month, even though there is no data. Is that possible?

Re: Creating a accumulated graph

sunny_talwar — Fri, 16 Sep 2016 16:15:03 GMT

Try this:

Table:

LOAD *,

MonthName(Eff_date) as MonthYear;

LOAD * INLINE [

Product, Eff_date

A, 2015-12-12

B, 2016-01-01

C, 2016-02-02

D, 2016-03-03

E, 2016-05-05

];

Join (Table)

LOAD MonthName(AddMonths(Min, IterNo() - 1)) as MonthYear

While AddMonths(Min, IterNo() - 1) <= Max;

LOAD Min(MonthYear) as Min,

Max(MonthYear) as Max

Resident Table;

Max:

LOAD Min(MonthYear) as Min

Resident Table;

LET vMin = Peek('Min');

DROP Table Max;

LinkTable:

LOAD MonthYear as ReportMonthYear,

MonthName(AddMonths(MonthYear, (-IterNo() + 1))) as MonthYear

Resident Table

While AddMonths(MonthYear, (-IterNo() + 1)) >= $(vMin);

Re: Creating a accumulated graph

Tue, 20 Sep 2016 06:56:02 GMT

Thank you! This work very well.

Re: Creating a accumulated graph

sunny_talwar — Tue, 20 Sep 2016 18:01:12 GMT

Awesome, I would suggest you to close down the thread by marking the correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny