Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Do this . Delete everything else
ColumnHeader:
LOAD [date dd.mm.yyyy] As Date,
Num([date dd.mm.yyyy]) As NumDate,
[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
(
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?
Same result unfortunately, and for some reason I cannot upload my example file to the forum
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
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.
Still not working im afraid, here comes an exampel file.
//O
Hi,
Please check the attached application.
Edit:
Added another Table for date as dimension with a different formula.
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
(
Temp2:
NoConcatenate
LOAD
[date dd.mm.yyyy],
num([date dd.mm.yyyy]) as NumDate,
[Client name] as ClientName,
[balance in EUR] as Balance
FROM
(
Resident ColumnHeader;
Drop Table ColumnHeader;
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;
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)