Creating a accumulated graph

[#1]

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

[krishnacbe]

Hi,

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

[sunny_talwar]

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);

[#4]

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?

[sunny_talwar]

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);

[#6]

Thank you! This work very well.

[sunny_talwar]

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