Problem to handle data with different dates

[#1]

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!

[marcus_sommer · Accepted solution]

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

[maxgro]

PFA

[MarcoWedel]

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

[#5]

Thank you so much for all your help!