Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

gianluca_ambrog
New Contributor II

One table, Two Date Fields and one Backlog

Hi all,
I am new in QLIK community and I am quite new in qlik experience. I have read the post "One table, Two Date Fields"  ( https://community.qlik.com/thread/136729?_ga=2.147659761.733376340.1515509018-2108658744.1514547763 ) about the solution to show 2 date fields in the same Chart.


I have a similar problem, I have to show in a chart a incident log table with 3 columns (two date fields that can contain different dates):
- incident number
- incident open date
- incident close date

I need to create a chart in which, for each calendar day, I show the total count of opened and closed incident.

And this is simple, implementing the same solution described in the post "One table, Two Date Fields" (see Marco Wedelsolution).


But I need a new more element to show in the chart: the "incident backlog", I mean the historical number of incident "still open" for each day.

The formula is:  "backlog day1" = "backlog day0" + "total count opened incident day 1" - "total count closed incident day 1"


I can't find out how to calculate this backlog.

Someone can help me?

1 Solution

Accepted Solutions

Re: One table, Two Date Fields and one Backlog

Hi,

one example using your sample data might be:

QlikCommunity_Thread_287509_Pic1.JPG

QlikCommunity_Thread_287509_Pic2.JPG

using [Date] as dimension and an expression like:

Count({$<IncidentTrend={"*open*"}>} DISTINCT [Incident Number])-Count({$<IncidentTrend={"*close*"}>} DISTINCT [Incident Number])

tabIncident:

LOAD [Incident Number],

    [Incident Open Date],

    If([Incident Close Date],[Incident Close Date]) as [Incident Close Date]

FROM [https://community.qlik.com/thread/287509] (html, codepage is 1252, embedded labels, table is @1);

tabLinkIncident:

CrossTable (IncidentTrend, Date)

LOAD [Incident Number],

    [Incident Open Date] as Opened,

    [Incident Close Date] as  Closed

Resident tabIncident;

tabCalendar:

LOAD *,

    Day(Date) as Day,

    WeekDay(Date) as WeekDay,

    Week(Date) as Week,

    WeekName(Date) as WeekName,

    Month(Date) as Month,

    MonthName(Date) as MonthName,

    Ceil(Month(Date)/3) as Quarter,

    QuarterName(Date) as QuarterName,

    Year(Date) as Year,

    WeekYear(Date) as WeekYear;    

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1<=MaxDate;

LOAD Min(Date) as MinDate,

    Max(Date) as MaxDate

Resident tabLinkIncident;

hope this helps

regards

Marco

7 Replies

Re: One table, Two Date Fields and one Backlog

Would you be able to provide some data with the expected output to help you better?

Partner
Partner

Re: One table, Two Date Fields and one Backlog

Hi Gianluca,

I think what you can do :

You can take second expression in the same chart as per the formula you shared above.

Then show your output by selecting Style tab in chart option with Stacked.

Hope this help you

Regards

Kamal

gianluca_ambrog
New Contributor II

Re: One table, Two Date Fields and one Backlog

Hi!

First of all thanks for the support.

Here you can find some data and the script.

Incident NumberIncident Open DateIncident Close Date
IN0517212602-12-201609-12-2016
IN0517212802-12-201612-12-2016
IN0517264702-12-201609-12-2016
IN0517389609-12-201612-12-2016
IN0517431509-12-201609-12-2016
IN0517495809-12-2016
IN0517503509-12-201609-12-2016
IN0517503609-12-201609-12-2016
IN0517503609-12-201609-12-2016
IN0517628009-12-2016
IN0517652209-12-201616-12-2016
IN0517657809-12-201618-01-2017
IN0517676309-12-2016
IN0517694709-12-201608-03-2017
IN0517725209-12-201611-01-2017
IN0517776009-12-201620-12-2016
IN0517783309-12-201625-01-2017
IN0519161812-12-201612-12-2016
IN0519163912-12-201612-12-2016
IN0519185712-12-201612-12-2016
IN0519267712-12-2016
IN0519396012-12-201627-12-2016

//////////////////////////////////////////////////////////////////////////////////////////////////////

tabIncident:

LOAD

    [Incident Number],

    [Incident Open Date] as Opened,

    [Incident Close Date] as Closed

FROM

$(XLS_FILE)

(ooxml, embedded labels, table is Incident);

tabLinkIncident:

CrossTable (IncidentTrend, Date)

LOAD

    [Incident Reference Number],

    Opened,

    Closed

Resident tabIncident;

tabCalIncident:

LOAD *,

    Day(Date) as Day,

    WeekDay(Date) as WeekDay,

    Week(Date) as Week,

    WeekName(Date) as WeekName,

    Month(Date) as Month,

    MonthName(Date) as MonthName,

    Ceil(Month(Date)/3) as Quarter,

    QuarterName(Date) as QuarterName,

    Year(Date) as Year,

    WeekYear(Date) as WeekYear;

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1<=Today();

LOAD RangeMin(Min(Opened),Min(Closed)) as MinDate

Resident tabIncident;

//////////////////////////////////////////////////////////////////////////////////////////////////////

Here the Chart I did.

Incident Trend.jpg

Here the Chart with the BackLog (grey line) I would like to obtain.

Incident Trend with Backlog.jpg

Re: One table, Two Date Fields and one Backlog

Hi,

one example using your sample data might be:

QlikCommunity_Thread_287509_Pic1.JPG

QlikCommunity_Thread_287509_Pic2.JPG

using [Date] as dimension and an expression like:

Count({$<IncidentTrend={"*open*"}>} DISTINCT [Incident Number])-Count({$<IncidentTrend={"*close*"}>} DISTINCT [Incident Number])

tabIncident:

LOAD [Incident Number],

    [Incident Open Date],

    If([Incident Close Date],[Incident Close Date]) as [Incident Close Date]

FROM [https://community.qlik.com/thread/287509] (html, codepage is 1252, embedded labels, table is @1);

tabLinkIncident:

CrossTable (IncidentTrend, Date)

LOAD [Incident Number],

    [Incident Open Date] as Opened,

    [Incident Close Date] as  Closed

Resident tabIncident;

tabCalendar:

LOAD *,

    Day(Date) as Day,

    WeekDay(Date) as WeekDay,

    Week(Date) as Week,

    WeekName(Date) as WeekName,

    Month(Date) as Month,

    MonthName(Date) as MonthName,

    Ceil(Month(Date)/3) as Quarter,

    QuarterName(Date) as QuarterName,

    Year(Date) as Year,

    WeekYear(Date) as WeekYear;    

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1<=MaxDate;

LOAD Min(Date) as MinDate,

    Max(Date) as MaxDate

Resident tabLinkIncident;

hope this helps

regards

Marco

Highlighted

Re: One table, Two Date Fields and one Backlog

another method could be to  Creating Reference Dates for Intervals

in this case leading to easier chart expressions (without the need for in-chart accumulation) but increasing your application size.

hope this helps

regards

Marco

gianluca_ambrog
New Contributor II

Re: One table, Two Date Fields and one Backlog

It works!!!

Marco, thank you very much.

Re: One table, Two Date Fields and one Backlog

you're welcome

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco