Suggestions for reducing rows in order to show Opening and Closing base

[sandeepgather]

We have an App which contains around 200M rows. It represents total number of customers we have at the end of each month. We need to create a view showing Opening Count, movements within the month like addition deletion and then Closing count for each month

	March	April
Opening	4000	3900
Addition	500	300
Deletion	600	100
Closing	3900	4100

Problem - We are currently duplicating the numbers of rows by loading the same Closing month file and increasing the date by 1 to make it as Opening count for next month. Is their any smarter way of implementing so that we can reduce the number of rows?

[vinieme12]

There's absolutely no need to duplicate the data

Just create a date bridge to link the monthend date to also associate is a opening date for next month

In charts refer to the Datetype field when doing calculations

Opening:  count({<DateType={'Opening'}>}CustomerID)

Closing : count({<DateType={'Closing'}>}CustomerID)

Difference:  count({<DateType={'Opening'}>}CustomerID)-count({<DateType={'Closing'}>}CustomerID)

raw:
Load Date(MonthEnd) as MonthEnd,CustomerID Inline [
MonthEnd,CustomerID
2022-03-31,1
2022-03-31,2
2022-03-31,3
2022-03-31,4
2022-04-30,1
2022-04-30,2
2022-04-30,3
2022-04-30,4
2022-04-30,5
2022-04-30,6
];

DateBridge:
Load Distinct MonthEnd
,MonthEnd as Date
, 'Closing' as DateType
resident raw;
Concatenate(DateBridge)
Load Distinct MonthEnd
, Date(MonthEnd+1) as Date
, 'Opening' as DateType resident raw;

MasterCalendar:
Load
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Date', recno()))-1 as mindate,
max(FieldValue('Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('Date');

Post an appropriate sample data in excel , if you need more clarity

[vinieme12]

Just create a bridge table to link the month end date to next month opening date

In charts:

Opening:  count({<DateType={'Opening'}>}CustomerID)

Closing: count({<DateType={'Closing'}>}CustomerID)

Difference: count({<DateType={'Opening'}>}CustomerID)-count({<DateType={'Closing'}>}CustomerID)

raw:
Load Date(MonthEnd) as MonthEnd,CustomerID Inline [
MonthEnd,CustomerID
2022-03-31,1
2022-03-31,2
2022-03-31,3
2022-03-31,4
2022-04-30,1
2022-04-30,2
2022-04-30,3
2022-04-30,4
2022-04-30,5
2022-04-30,6
];

DateBridge:
Load Distinct MonthEnd
,MonthEnd as Date
, 'Closing' as DateType
resident raw;
Concatenate(DateBridge)
Load Distinct MonthEnd
, Date(MonthEnd+1) as Date
, 'Opening' as DateType resident raw;

MasterCalendar:
Load
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Date', recno()))-1 as mindate,
max(FieldValue('Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('Date');

exit script;

Please post some sample data in excel that best represents your actual data ; if you need more clarity

[vinieme12]

You can simply create a bridge table that links one month end date to   a monthend and monthstart date

please post some sample data