topic Set Analysis Aggregate two different date fields in App Development

Set Analysis Aggregate two different date fields

lanlizgu — Sat, 16 Nov 2024 19:38:00 GMT

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?

Re: Set Analysis Aggregate two different date fields

Anil_Babu_Samineni — Wed, 20 Nov 2019 13:47:19 GMT

Try this?

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

Re: Set Analysis Aggregate two different date fields

lanlizgu — Wed, 20 Nov 2019 14:55:22 GMT

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.

Re: Set Analysis Aggregate two different date fields

lanlizgu — Wed, 20 Nov 2019 16:49:45 GMT

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.