Could you please help with following Bo 5 problem?
BO query (snapshot attached) generates for each Account list of all Orders and their OrderDates. For each Account I need to get the last (the biggest) OrderDate.
For this I used new variable Last Order Date=Max() In ().
For some reason by typing this formula the Definition tab with Qualification (Dimension, Measure, Detail) becomes inactive (greyed). So I can not choose whether this is Demension or Measure. But the icon assigned to it is pink (Measure).
I want to filter out the Last orders of each account only, but I am not allowed to do it, because all other variables associated with the previous one are of the same qualification (greyed) and filtering is disabled.
I appreciate any help on this!
Ondrej BOissue.zip (105.0 KB)
Hi, Welcome to B:bob:B! Your question is a fairly frequent one, and as a result it is in our “FAQ”. Look for the option about filtering on aggregate functions (like sum or max).
Now I know that filtering on Sum function is really not working in BO.
Is there any other solution how I can filter out orders with last order date?
I mean except of returning data directly from the query.
And I have nothing to filer on before I calculate last order date.
Create a flag that will give you 1 for each account and order_date where order_date = max(order_date) and 0 for all other dates. Then do a rank of account ID object based on this flag and show only top 1.
The ranking ranks a dimension based on values of a measure you select in the dialog.
As I wrote previously - create a flag that will have only 2 values. Either 1 (for the account IDs where order date = max(order date) ) or 0 for all other dates.
Now do ranking of based on the values of this new flag and select TOP 1 - it means it will filter only the records where flag has value 1. All records where flag=0 should be filtered out.
It means regardless of the number of records you have in the report the flag has only 2 values (0/1) and TOP 1 ranking is always possible.
It is very likely due to the calculation context in which the formula/variable is used in the report.
I am not sure about such principle. However I can share with you my approach in building formulas:
When I suggested you the solution I had created a simple report with accounts and dates - very simple 2-columns report, just to test what I was doing. At the beginning I created the flag with the same formula as you:
=If (<ORDER_DATE> = Max(<ORDER_DATE>) In <ACCOUNT_ID>) Then 1 Else 0
and the RANK 1 did not work.
So I put the formula (not the flag variable) into the column and went to menu Data → Define as Variable and here I selected the option “Evaluate the formula in its context”. So BO knew what the context of my original formula was and created a new formula in its context. Then I defined the flag with this new formula and it worked.
So there is no mystery in it after all, I just used what BO provided me with
(BOB member since 2002-06-06)
(BOB member since 2003-11-27)
(BOB member since 2003-11-27)