Got a good one for you - just looking for some guidance or suggestions as to the best way to tackle this one.....
I have a source table with the following fields:
Date Account SubAccount Amount
and I need to present the data as:
Date Account Sum(Amount) Sum(Amount) for previous day Diff: Sum(Amount) - Sum(Amount) for previous day
So what is the best way? Is there some function I can use to determine the previous day and do it all in a chart? Or should I do some calcs on import and derive the Amount for the previous day at SubAccount level?
(Note - previous day is not the same as (Day) - 1 as we have no data over the weekend. This is stumping me slightly at the moment so any suggestions for this would also be most welcome.)
Ok, I've just discovered the FirstWorkDate function which seems to help in deriving the previous working day. So now I just need to figure out how to work out Sum(Amount) by Account for that previous day....
You can solve it with a date island and a couple sum(if(...)) expressions, but that's asking for big performance problems on large data sets. Here's a data modeling approach that avoids the performance issues at the cost of some additional complexity. It's comparing this year to the previous year, but same idea as comparing one day to the previous working day. You can't do your difference with a pivot table like this. For that, you could create separate expressions for today and yesterday using set analysis, then subtract. I can come up with an example closer to what you need if you can't work it out from these hints.
Thanks John, that was very helpful and an interesting approach.
I've not done any set analysis yet so I don't think it would be too wise to start with this one. For my purposes I think I'll do some pre-processing in Access to create a new source file and take it from there.
