Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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