Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
Not applicable
Author

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

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

View solution in original post

14 Replies
Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

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
Partner
Partner

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.

Not applicable
Author

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

//O

Not applicable
Author

Hi,

Please check the attached application.

Edit:

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

Not applicable
Author

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;

Not applicable
Author

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
Partner
Partner

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)