Set analysis: Filter for all dates prior to current date selection

[JeffQV]

Assume there is a log of daily financial transactions.

The user has applied a date filter and is just looking at the previous month's transactions (e.g. it's June so they are looking at May's transactions)

The catch is that they also want to know the sum of the daily transactions up to the filtered date range (so in the previous example the sum of all transactions up to April 30)

I tried different variations of the following but all I get back is formatting error messages:

=sum({1<[Transaction - Date]={ "<$(=min([Transaction - Date])" }>} [Transaction - Amount])

I have the vague and uneasy feeling that I'm missing something straightforward here.

Thank you!

[swuehl]

There is a parenthesis missing in your dollar sign expansion.

=sum({1<[Transaction - Date]={ "<$(=min([Transaction - Date]))" }>} [Transaction - Amount])

You may also need to format the min date using the same format as used in the field, and if your user basically makes selections in other calendar field (e.g. Month), you would need to clear selections in all these fields(edit: you don't need this if you are using set identifier 1)

=sum({1<[Transaction - Date]={ "<$(=Date(min([Transaction - Date]),'YourFieldFormat'))" } >} [Transaction - Amount])

[JeffQV]

Thanks for your reply and the statement works (it appears that the Date() is necessary).

However, I actually need to respect other selections (I actually want this calculation in a pivot table)

Of course when I use $ instead of 1 then I just get back 0 as the sum.

Is there a way to keep the other selections?

(and it's safe to assume that this is the only date field)

[swuehl]

you can also use $, but clearing any user selections in conflicting field values:

=sum({$<[Transaction - Date]={ "<$(=Date(min([Transaction - Date]),'YourFieldFormat'))" }, MonthField=, YearField=, YearMonthField= >} [Transaction - Amount])

[JeffQV]

Thanks again and I tip my hat to you for your help 🙂

In my case however there could be a couple dozen or more dimensions to select on.

Though clearing the user selections from all possible dimensions will work it seems a little heavy handed.

I'd actually like the reverse - keep all selections except for the transaction date (well, except when I'm looking at the min() because obviously at that point I'd like to keep the current selection)

[swuehl · Accepted solution]

Jeff,

not sure if I can follow.

The dollar sign expansion including the min() function will be evaluated before the set expression even gets parsed, like a preprocessor text replacement.

So this will keep all selections and overwrites the set of Transaction Date values with a search for values below the default set Min Transaction date:

=sum({$<[Transaction - Date]={ "<$(=min([Transaction - Date]))" }>} [Transaction - Amount])

If you don't have any other user selections that creates an incompatible record set w.r.t the Transaction Date modified set, this should work (but if you user selects a Month-Year field value of June 2016, the set modifier on Transaction Date will create a set for dates before June 2016, so this incompatible record set will not work).

So you don't need to clear every user selections, only those problematic ones (in general, user selections in calendar fields).

You can also use something like

=sum({1<[Transaction - Date]={ "<$(=min([Transaction - Date]))" }, Company = $::Company>} [Transaction - Amount])

to use a user selection in combination with the set identifier 1.

[JeffQV]

I was still struggling with the expression (mapped to my real data model) and so I created the following inline table:

LOAD * INLINE
[
Transaction - Date, Transaction - Amount
2016-01-01,10
2016-02-01,10
2016-03-01,10
2016-04-01,10
2016-05-01,10
2016-06-01,10
2016-07-01,10
2016-08-01,10
2016-09-01,10
2016-10-01,10
2016-11-01,10
2016-12-01,10
];

Your expression works perfectly. When I select May 1 in a list box, the text box with the expression correctly shows $40 ($10 per month for the first 4 months).

Thanks again for your help!