Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andyquirin
Partner
Partner

Turning Transactional Dates into Calendar Months

Hi Qlik Community,

I've hit a wall and am hoping you can help. I've got a transactional table of Assets that have Created Dates and End of Life Dates and Asset Status (Live, Failed). I've been asked to create a simple bar graph that shows how many assets were alive for a given month. In order to do so, I need to tie these assets to months (which aren't in the data) and do a distinct count of assets which were online between their Born On date and End of Life Date.

The data looks similar to this:   

Asset IDCreated_DateFailure_Dateasset_status
11/1/20154/7/2015Failed
21/16/2015Passed
31/31/20155/19/2015Failed
42/15/2015Passed
53/2/2015Passed
63/17/20155/21/2015Failed
74/1/2015Passed
84/16/2015Passed
95/1/2015Passed

I'm trying to transform it to something like this:

YearMonthCount of Assets
2015 Jan3
2015 Feb4
2015 Mar9
2015 Apr7

How do I create this YearMonth field and count the assets for each of the months if the month falls between the assets created and EOL date?

Thanks for your help!

Andy

1 Solution

Accepted Solutions
sunny_talwar

Not sure how you getting Mar and Apr number, but may be this:

Script:

Table:

LOAD [Asset ID],

    Created_Date,

    Failure_Date,

    asset_status,

    If(Len(Trim(Failure_Date)) = 0, MakeDate(2015, 5, 31), Failure_Date) as Temp_End_Date

FROM

[https://community.qlik.com/thread/208100]

(html, codepage is 1252, embedded labels, table is @1);

Temp:

LOAD Min(Created_Date) as minDate,

  Max(Temp_End_Date) as maxDate

Resident Table;

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

 

Calendar:

LOAD MonthName($(varMinDate) + IterNo() - 1) as MonthYear, 

    Date($(varMinDate) + IterNo() - 1) as Date 

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Left Join (Table)

IntervalMatch (Date)

LOAD Created_Date,

  Temp_End_Date

Resident Table;

Left Join (Table)

LOAD *

Resident Calendar;

DROP Table Calendar;


Capture.PNG

View solution in original post

5 Replies
bill_markham
Champion III
Champion III

For creating your YearMonth field the MonthName() function should suffice.

For the rest I reckon you need the IntervalMatch() function, as described by HIC in this Blog PostIntervalMatch

sunny_talwar

Not sure how you getting Mar and Apr number, but may be this:

Script:

Table:

LOAD [Asset ID],

    Created_Date,

    Failure_Date,

    asset_status,

    If(Len(Trim(Failure_Date)) = 0, MakeDate(2015, 5, 31), Failure_Date) as Temp_End_Date

FROM

[https://community.qlik.com/thread/208100]

(html, codepage is 1252, embedded labels, table is @1);

Temp:

LOAD Min(Created_Date) as minDate,

  Max(Temp_End_Date) as maxDate

Resident Table;

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

 

Calendar:

LOAD MonthName($(varMinDate) + IterNo() - 1) as MonthYear, 

    Date($(varMinDate) + IterNo() - 1) as Date 

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Left Join (Table)

IntervalMatch (Date)

LOAD Created_Date,

  Temp_End_Date

Resident Table;

Left Join (Table)

LOAD *

Resident Calendar;

DROP Table Calendar;


Capture.PNG

View solution in original post

sfatoux72
Partner
Partner

‌Create a YearMonth Field:

load

  Date(Date#(YearMonth, 'MM/YYYY'), 'YYYY MMM') as YearMonth

inline [

YearMonth

01/2015

02/2015

...

12/2015

];

and and use this expression :

=count(if((Created_Date <= MonthEnd(YearMonth)) and (Date(alt(Failure_Date,Date#('01/2100','MM/YYYY'))) >= MonthStart(YearMonth)) , [Asset ID]))

maxgro
MVP
MVP


1.png



SET DateFormat='M/D/YYYY';

Z:

load

  *,

  If(Len(Trim(Failure_Date)) = 0, date(MakeDate(2015, 5, 31)), Failure_Date) as [End of Life Dates] 

inline [

Asset_ID, Created_Date, Failure_Date, asset_status

1, 1/1/2015, 4/7/2015, Failed

2, 1/16/2015, ,Passed

3, 1/31/2015, 5/19/2015, Failed

4, 2/15/2015, ,Passed

5, 3/2/2015, ,Passed

6, 3/17/2015, 5/21/2015, Failed

7, 4/1/2015, ,Passed

8, 4/16/2015, ,Passed

9, 5/1/2015, ,Passed

] ;

load

  Asset_ID,

  Date(AddMonths(MonthStart(Created_Date), IterNo()-1), 'YYYY MM') as Month

Resident Z

While AddMonths(MonthStart(Created_Date), IterNo()-1) <= MonthStart([End of Life Dates]) ;

andyquirin
Partner
Partner
Author

Thanks Sunny T! This worked perfectly - super impressed at your turn around.

I appreciate your help.

Best,

Andy