Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating a accumulated graph

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

6 Replies
krishnacbe
Partner - Specialist III
Partner - Specialist III

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

Capture.PNG

Not applicable
Author

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


Capture.PNG

Not applicable
Author

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