topic Re: Problem to handle data with different dates in QlikView

Problem to handle data with different dates

Sat, 23 May 2015 15:07:04 GMT

Hi all,

I have 4 different dates in the data which indicate 4 different stages. The name of the 4 dates are:

data issue date

data received date

data released date

data executed date

All of them are in the same row with an amount.

Below is an example:

data issue date	data received date	data released date	data executed date	Amount
1-1-2015	1-15-2015	2-1-2015	3-1-2015	1000
1-2-2015	1-15-2015	2-15-2015	3-2-2015	2000

I used the data issue date as the base to create some time fields like Year, Month, Quarter, Year-month, Year-quarter, ... etc, so that I can use those time fields to control the data.

What I would like to do now is to compare the sum of the amount in the same bar chart, under the same time period, for example:

Jan 2015 Total amount of data issue date with dates in Jan-2015 (i.e. 3000)

Total amount of data received date with dates in Jan-2015 (i.e. 3000)

Total amount of data released date with dates in Jan-2015 (i.e. 0)

Total amount of data executed date with dates in Jan-2015 (i.e. 0)

Feb 2015 Total amount of data issue date with dates in Feb-2015 (i.e. 0)

Total amount of data received date with dates in Feb-2015 (i.e. 0)

Total amount of data released date with dates in Feb-2015 (i.e. 3000)

Total amount of data executed date with dates in Feb-2015 (i.e. 0)

... etc.

I have no idea what I should do in the script or in the expression in order to achieve this goal. Would you please share some lights to me to tackle this problem? Thanks for all your help first!

Re: Problem to handle data with different dates

marcus_sommer — Sat, 23 May 2015 15:40:09 GMT

You could try to use several calendars like in Canonical Date and/or using several set analysis expressions like:

sum({<[received date] = {">=$(=monthstart([issue date]))<=$(=monthend([issue date]))"}>} Amount)

- Marcus

Re: Problem to handle data with different dates

maxgro — Sat, 23 May 2015 16:22:25 GMT

PFA

Re: Problem to handle data with different dates

MarcoWedel — Sat, 23 May 2015 20:24:10 GMT

Hi,

an implementation of the proposed canonical date calendar using your data could look like:

tabData:
LOAD RecNo() as %Key, *
FROM [https://community.qlik.com/thread/165298] (html, codepage is 1252, embedded labels, table is @1);

tabDates:
CrossTable (DateTypeTemp, Date)
LOAD * Resident tabData;

Right Join (tabDates)
LOAD Distinct
    DateTypeTemp,
  SubField(DateTypeTemp,' ',2) as DateType
Resident tabDates
Where DateTypeTemp like '*date';

DROP Field DateTypeTemp;

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,
    Dual('Q'&Ceil(Month(Date)/3),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 tabDates;

hope this helps

regards

Marco

Re: Problem to handle data with different dates

Sun, 24 May 2015 02:10:03 GMT

Thank you so much for all your help!