Weekly cash report (table with different formulas per row and per column)

[BartVA]

Hello,

I need to make a weekly cash report, in a table, with different formulas for each row. I've read about ValueList which may be helpful, but I need also to fill the table up towards the right with a new column for each new week, a bit like a pivot table, and the formulas need to take into account the weeknumber in the column header.

As a simplified example I have (see also attached Excel)
- a BankStatement table, with a time series of dates and amounts.
- a Sales table, with dates and amounts
- a Purchase table, with dates and amounts

and I need a result like this:

	2024-01	2024-02	2024-03
cash week start	Latest BankStatement for date < end of above week	same as left, for next week	same as left, for next week
customers in	sum of Sales for above week	same as left, for next week	same as left, for next week
suppliers out	sum of Purchase for above week	same as left, for next week	same as left, for next week
total	C3+C4-C5	same as left, for next week	same as left, for next week

How is the best way to set this up?

[LRuCelver]

You can transform the data to have the following structure:

and then display it in a pivot table:

Rows: Week
Columns: KPI_Name (sorted by "Max(KPI_Sort)")
Values: Sum(Value)

Here is the script used:

BankStatements:
NoConcatenate Load
    "Date",
    BankStatement
From [lib://AttachedFiles/weekly cash report.xlsx]
(ooxml, embedded labels, table is BankStatements);

Sales:
NoConcatenate Load
    "Date",
    Sales
From [lib://AttachedFiles/weekly cash report.xlsx]
(ooxml, embedded labels, table is Sales);

Purchase:
NoConcatenate Load
    "Date",
    Purchase
From [lib://AttachedFiles/weekly cash report.xlsx]
(ooxml, embedded labels, table is Purchase);



Total:
NoConcatenate Load
	Date,
    Sum(BankStatement) as BankStatement
Resident BankStatements
Group By Date;
Join Load
	Date,
    Sum(Sales) as Sales
Resident Sales
Group By Date;
Join Load
	Date,
    Sum(Purchase) as Purchase
Resident Purchase
Group By Date;



Transformed:
NoConcatenate Load
	Date,
    'cash week start' as KPI_Name,
    1 as KPI_Sort,
    BankStatement as Value
Resident BankStatements;
Concatenate Load
	Date,
    'customers in' as KPI_Name,
    2 as KPI_Sort,
    Sales as Value
Resident Sales;
Concatenate Load
	Date,
    'suppliers out' as KPI_Name,
    3 as KPI_Sort,
    Purchase as Value
Resident Purchase;
Concatenate Load
	Date,
    'total' as KPI_Name,
    4 as KPI_Sort,
    If(IsNull(BankStatement), 0, BankStatement) + If(IsNull(Sales), 0, Sales) - If(IsNull(Purchase), 0, Purchase) as Value
Resident Total;

Drop Tables BankStatements, Sales, Purchase, Total;



Dates:
NoConcatenate Load Distinct
	Date,
    WeekName(Date) as Week
Resident Transformed;

[BartVA]

Fabulous, looks great, thanks!

There's just one detail, if I understand correctly, for the "cash week start", you also take the sum of the Bankstatements for that week, while I need only the last value for that week. So for week 1 that would be 1100, for week 2 1550, for week 3 1560. I suppose I should make a new BankStatementTransformed table, which contains per week 1 value, and continue from there? Could you help with that? Taking into account also that there might not be a value for that week in the original table, so it's the latest value from any date that's smaller than the latest day of the week in question.

(So the same value might return week after week in the BankStatementTransformed table, until a new value appears in the original BankStatement table).

[LRuCelver · Accepted solution]

You are right. Sorry, I totally missed that.

Here is a slightly changed and more aggregated version:

Data:
NoConcatenate Load
    Date,
    WeekName(Date) as Week,
    BankStatement
From [lib://AttachedFiles/weekly cash report.xlsx]
(ooxml, embedded labels, table is BankStatements);

Inner Join Load
	Date(Max(Date)) as Date,
    Week
Resident Data
Group By Week;

Join Load
	Week,
    Sum(Sales) as Sales
Group By Week;
Load
    WeekName(Date) as Week,
    Sales
From [lib://AttachedFiles/weekly cash report.xlsx]
(ooxml, embedded labels, table is Sales);

Join Load
	Week,
    Sum(Purchase) as Purchase
Group By Week;
Load
    WeekName(Date) as Week,
    Purchase
From [lib://AttachedFiles/weekly cash report.xlsx]
(ooxml, embedded labels, table is Purchase);

Drop Field Date From Data;



Transformed:
NoConcatenate Load
	Week,
    'cash week start' as KPI_Name,
    1 as KPI_Sort,
    BankStatement as Value
Resident Data
Where not IsNull(BankStatement);

Concatenate Load
	Week,
    'customers in' as KPI_Name,
    2 as KPI_Sort,
    Sales as Value
Resident Data
Where not IsNull(Sales);

Concatenate Load
	Week,
    'suppliers out' as KPI_Name,
    3 as KPI_Sort,
    Purchase as Value
Resident Data
Where not IsNull(Purchase);

Concatenate Load
	Week,
    'total' as KPI_Name,
    4 as KPI_Sort,
    If(IsNull(BankStatement), 0, BankStatement) + If(IsNull(Sales), 0, Sales) - If(IsNull(Purchase), 0, Purchase) as Value
Resident Data;

Drop Tables Data;

[BartVA]

That's simply wonderful, thank you very much!

[BartVA]

I just realized there might be another problem here. If there has been no new bank statement for let's say 2 weeks, then the Max Date in the Grouping by Week will have no value?
Even if there are no bank statements during several weeks, I still should see the last reported bank statements (from weeks ago then, repeated week after week until a new bank statement is entered).

Also, the bank statements in reality come from various banks, and in the final report should be added up. So the bank statement might look like this (adapted Excel attached).

So the final report needs to take the last entry per bank before the date in question (which might be many weeks ago), and then sum the banks up...

[BartVA]

I branched my latest reply/follow-up question to a new topic "Sum of latest values, with multiple dimensions" since my first question here was answered, and to not complicate this topic too much. Thanks again @LRuCelver !