Asof Table - but not including current month

Thu, 29 Sep 2016 14:39:05 GMT

Hi All,

I have a situation where I want to look at the sum(sales) for a rolling previous 3 months BUT NOT including this months' sales.

Hence when I Qlik on the current quarter I want to see and report the Sales for the previous quarter.

I have looked at Mr. Chronstrom and Harmsen's solutions where you outer join the calendar to itself but that assumes a situation

where Date of Sale <= Date (Date in second calendar table outer joined to calendar table).

What I did was the following to get the Merchant ID and (Sale)Amount and previous three month amount and average onto the same line in the transaction table which works nicely but I am not sure how to use this to calculate what I need:

SWIPE_AGGR_TMP:

LOAD

[Merchant ID] as [%Key Merchant],

date([Date Posted]) as [Date Posted],

MonthStart([Date Posted]) as [Date Posted Month],

MonthStart([Date Posted]) as DATE,

[Amount],

Interchange

from

[QVD\Transactions.QVD] (qvd)

where

date([Date Posted]) <= monthend(today()) and

date([Date Posted]) >= $(vStartDateTransactions);

//CONCATENATE TO GET THREE MONTHS DATA INTO ONE MONTH

//conc1

SWIPE_AGGR:

NoConcatenate

LOAD

[%Key Merchant],

AddMonths(DATE,1) as DATE,

DATE as TRAN_DATE,

Amount as AMOUNT,

0 as CYCLE

Resident

SWIPE_AGGR_TMP;

//conc 2

Concatenate(SWIPE_AGGR)

LOAD

[%Key Merchant],

AddMonths(DATE,2) as DATE,

DATE as TRAN_DATE,

Amount as AMOUNT,

1 as CYCLE

Resident

SWIPE_AGGR_TMP;

//conc 3

Concatenate(SWIPE_AGGR)

LOAD

[%Key Merchant],

AddMonths(DATE,3) as DATE,

DATE as TRAN_DATE,

Amount as AMOUNT,

2 as CYCLE

Resident

SWIPE_AGGR_TMP;

noconcatenate

////3 MONTHS SWIPE AMOUNT & AVERAGE////////

SWIPE_AGGR_GROUP:

LOAD

[%Key Merchant],

DATE,

sum(AMOUNT)/3 as Avg_Amount_3Month,

sum(AMOUNT) as Amount_3Month

Resident

SWIPE_AGGR

group by

[%Key Merchant],

DATE;

drop table SWIPE_AGGR;

//SWIPES: JOIN BACK TO ORIGINAL SWIPE TABLE TO GET THE 3 MONTHS AMOUNT & AVERAGE ONTO RECORD

left join (SWIPE_AGGR_TMP)

LOAD

[%Key Merchant],

DATE,

Amount_3Month,

Avg_Amount_3Month

RESIDENT

SWIPE_AGGR_GROUP;

drop table SWIPE_AGGR_GROUP;

What I want to display in the front end is:

1. Analyze the previous three months (quarter) sales when clicking on this quarter on a rolling basis. As an example:

When selecting 2015-Q1 should show sum of Amount for 2014-Q4,

When selecting 2015-Q2 should show sum of Amount for 2015-Q1,

When selecting 2015-Q3 should show sum of Amount for 2015-Q2,

When selecting 2015-Q4 should show sum of Amount for 2015-Q3,

When selecting 2016-Q1 should show sum of Amount for 2015-Q4,

etc.

Another way of stating the problem is the following:

When selecting 2015-Q1 should show Avg_Amount_3Month for the first month of 2015-Q1 (as per my script above) but display as value for the whole 2015-Q1

When selecting 2015-Q2 should show Avg_Amount_3Month for the first month of 2015-Q2 (as per my script above) but display as value for the whole 2015-Q2

When selecting 2015-Q3 should show Avg_Amount_3Month for the first month of 2015-Q3 (as per my script above) but display as value for the whole 2015-Q3

When selecting 2015-Q4 should show Avg_Amount_3Month for the first month of 2015-Q4 (as per my script above) but display as value for the whole 2015-Q4

When selecting 2016-Q1 should show Avg_Amount_3Month for the first month of 2015-Q5 (as per my script above) but display as value for the whole 2016-Q1

Any ideas would be greatly appreciated, for personal contact please mail me at dirk101jacobs@gmail.com.

Kind Regards,

Dirk Jacobs

topic Asof Table - but not including current month in QlikView

Asof Table - but not including current month