6 months average based on max date - 6 months

Question & Answer
,
1

Hi all,
I want to find average of a field ‘amount’ based on date calculations like below:
It should find the max date where the customer purchased an item and it should subtract this date from its relative last 6 months to find average.

Functionally something like this: average (amount, datediff (max(invoice_date), max(invoice_date) - 6 months))

but QS does not support aggregation within aggregation.
any comments?

2

@sakshisl yes. QS doesn’t support that. you have to do it before bringing the data to the SPICE

3

@sakshisl

  1. Use maxOver to find the latest date.
  2. Use addDateTime on the result from step 1 to find the date 6 months before the max date.
  3. Find the latest amount.
  4. Find the amount corresponding to the date in step 2.

What do you want to do after you get the 2 amounts? Do you want to add them up and divide by 2?

4

Hi David thanks for the reply,
I want to do average of all the amounts/purchases that fall between maxdate of purchase of customer and maxdate - 6 months. The approach you suggested would just give me the two amounts and not the average.
I did try to achieve this by using top and bottom filter but that filter works on month to month basis rather taking the exact dates which changes my average.

5

Can you try something like this? Adjust the partition as needed. I’m not sure if you’re looking for an average for each customer or an average across all customers. Can you describe the visual that you’re trying to create?

maxdate:

maxOver(invoice_date, [customer], PRE_AGG)

Average:

avg(
	ifelse(
		invoice_date <= maxdate AND invoice_date > addDateTime(-6, MM, maxdate),
		amount,
		null
	),
	[customer]
)
6

thank you this worked!