Set Analysis Aggregate two different date fields

[lanlizgu]

Hi,

I have a table with clients info, including the last month day of the subscription in YYYYMMDD format.

In another table I have the sales last month day info of these clients by date (YYYYMMDD).

The thing is that I want to have a table with the subscription month as dimension and as measure the sales data for  the previous month.

I am not able to aggregate correctly it.

If I add multiple sums I am able to obtain the data, but I want it in one formula in an automatic way and without needing of adding data:

=sum({< SUBSCRIPTION_DATE={'20181031'}, DATE={'20180930'}>}SALES)
+
sum({< SUBSCRIPTION_DATE={'20181130'}, DATE={'20181031'}>}SALES)

Any suggestion?

[Anil_Babu_Samineni]

Try this?

=sum({< SUBSCRIPTION_DATE={'20181031', '20181130'}, DATE={'20190930', '20191031'}>}SALES)

[lanlizgu]

Hi,

I don't want to add dates manually but do it automatically.

Moreover, the dimension is the subscription date. Therefore, your expression is not correct, as in the 2018 October value of the subscription date dimension I will also see the sales data for October 2018, when I just want for thie 2018 October  subscription date to display the sales of 2018 September.

The formula requires somekind of aggregation by subscription date and interval filtering of the month sales.

Thank you.

Best regards.

[lanlizgu]

With the following sample data 

Client:
LOAD * INLINE [
Client_ID, SUBSCRIPTION_DATE
1, 31/08/2019
2, 30/09/2019
3, 31/10/2019
4, 31/08/2019
5, 30/09/2019
6, 31/10/2019
];

Sales:
LOAD * INLINE [
Client_ID, DATE, SALES
1, 31/07/2019, 14
1, 31/08/2019, 3
1, 30/09/2019, 11
1, 31/10/2019, 10
2, 31/08/2019, 8
2, 30/09/2019, 6
2, 31/10/2019, 8
3, 30/09/2019, 15
3, 31/10/2019, 13
4, 31/07/2019, 7
4, 31/08/2019, 16
4, 30/09/2019, 16
4, 31/10/2019, 12
5, 31/08/2019, 10
5, 30/09/2019, 15
5, 31/10/2019, 7
6, 30/09/2019, 18
6, 31/10/2019, 2
];

and the following table:

dimension: SUBSCRIPTION_DATE

measure =sum({< SUBSCRIPTION_DATE={'31/10/2019'}, DATE={'30/09/2019'}>}SALES)
+
sum({< SUBSCRIPTION_DATE={'30/09/2019'}, DATE={'31/08/2019'}>}SALES)

I get the data for septemberand october

The thing is that I don't want to add the dates manually as in the current measure and I want to calculate the data for all the months.