Show (and rank) largest account balance moves Today Vs. Yesterday

[#1]

Hi,

I'm trying to figure out how to show account balance moves (today vs yesterday), and then rank them, showing the biggest movers in ascending order.

For some reason I cannot get my formula to work, returning null values instead. Any ideas?

sum({<Date={$(=Date)}>}Accountbalance)) - (sum({<Date={$(=Date-1)}>}Accountbalance))

Thanks in advance,

Olle

[#2]

sum({<Date={$(=Date)}>}Accountbalance)) - (sum({<Date={$(=Date-1)}>}Accountbalance))

You've got an extra bracket after the first set-analysis. => sum({<Date={$(=Date)}>}Accountbalance) - (sum({<Date={$(=Date-1)}>}Accountbalance))

Does that give you the expected answer?

[#3]

Same result unfortunately, and for some reason I cannot upload my example file to the forum

[#4]

Can't check it right now but seem to recall you'll need to rewrite the modifiers to Date={"$(=Date-1)"}.

I'll check it ASAP

[simenkg]

sum({$<Date={$(=max(Date))}>}Accountbalance) - sum({$<Date={$(=max(Date)-1)}>}Accountbalance)

Calling date without an aggregate function (Max) will return an Array with multiple values. Date - 1 will return null.

[#6]

Still not working im afraid, here comes an exampel file.

//O

[#7]

Hi,

Please check the attached application.

Edit:

Added another Table for date as dimension with a different formula.

[#8]

Thanks Syed! That is exactly what Im looking for.

However, now that I try to implement this in my master script (loading thousands of rows) I cannot get it to work. Im getting multiple synthetic keys and nonsense ´figures Looks like I cannot apply the resident/drop table strategy when loading from a specific doc?

Thanks in advance,

Olle

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ColumnHeader:
LOAD [date dd.mm.yyyy] As Date,
     [Client no] as ClientNumber,
     [Client name] as ClientName,
     [delivery system] as System,
     [Product name]as ProductName,
     [branch no] as BranchNumber,
     [account no]as AccountNumber,
     [balance in EUR] as Balance

FROM

(ooxml, embedded labels, header is 2 lines, table is [balance amounts end of day]);

Temp2:
NoConcatenate
LOAD
    [date dd.mm.yyyy],
    num([date dd.mm.yyyy]) as NumDate,
    [Client name] as ClientName,
    [balance in EUR] as Balance

FROM

(ooxml, embedded labels, header is 2 lines, table is [balance amounts end of day]);

Resident ColumnHeader;  
Drop Table ColumnHeader;

[#9]

Hi,

Correct the Resident part. it should be like this.

Temp2:
NoConcatenate
LOAD
    [date dd.mm.yyyy],
    num([date dd.mm.yyyy]) as NumDate,
    [Client name] as ClientName,
    [balance in EUR] as Balance

Resident ColumnHeader;  

Drop Table ColumnHeader;

[simenkg]

You can't have Date as a dimension when you specify the Date in the set analysis.

With dimension Client the following expression does what you want:

sum({$<Date={"$(=Date(max(Date)))"}>}Accountbalance) - sum({$<Date={"$(=Date(max(Date)-1))"}>}Accountbalance)