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
