As a shortcut you might want to try using OBIEE to generate the sql. i.e. If you can create the analysis in answers that gives exactly what you want for the prompt then you can then set your diagnostics high, view the log file, get the physical sql and just copy that into the prompt area.

re: opaque view and some sort of pivot - I think you need listagg; - https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm Or if you have a very much earlier version of Oracle DB; - https://asktom.oracle.com/pls/asktom/f?p=100:11:::::p11_question_id:229614022562

On your formula; - SUM(CASE WHEN "Worker"."Termination Date" >= TIMESTAMPADD(SQL_TSI_YEAR, -(1),"Time"."Calendar Month Start Date") THEN 1 else 0 END) Is the effect that you were seeking?

Hi Mark, no - this was never necessary - even back to 10g.

It is not on the certification matrix beyond AIX 6.1, but then OBIEE 10g is not supported either.... You really should be upgrading!

One for end user education ;-)

Have you double checked that the user in question has not applied customisations - which can include user specified filters? You could get the user to clear all customisations to see if this resolves the issue, or look in the catalogue to see if you could see customisations there for that user.

Like in good fiction, on this board do not tell, show. Show the forum; - Your function. Your answers analysis and presentation format. Your physical sql. Your error. Otherwise we are stuck with those of us who know that 'By' works and you who cannot get 'By' to work - help us bridge that gap, we need to see through your…

I think you may have a side interest in BRIO? Gianni's Brio

Thanks for this - was trying to find a reference that confirmed the opinion that sum( ...by ) was no longer supported - was failing - thanks for confirming why!!

Would this work for you - the principal of it and the underlying database; - https://sonra.io/2011/06/01/time-series-in-obiee-analytic-functions-and-range-window-as-an-alternative-to-obiee-ago-func…

You could also use evaluate_aggregate function to plumb into your database functions to get this - assuming your DB supports sum by...

Sorry, maybe being slow, suffering from man flu here - are you asking for sum of last 3 max values? Or do you just want sum of last three months?

You need to use time series function - period rolling - see below link. OBIEE: Time Series Functions OBIEE

Okay, see my other responses on your other thread, again in the rpd duplicating your existing sum measure and pinning it to state would be a good alternative.

Hate it when they do that - that was really useful functionality!! You could also achieve similar by using the FILTER function in conjunction with your state and any other parameters filtering.

Hi Carsten, Essbase with OBIEE - not what it could / should be - which as a hybrid EPM / OBIEE person disappoints me greatly! Can you; - 1. back up your cube 2. Keep a copy of your rpd "as is" 3. delete the time hierarchy that is appearing in the DB from Essbase 4. import the cube in OBIEE 5. Copy the original time…

Sorry, thought I remembered you could use BY on the other aggregate functions also, but struggled to find a reference, but this one says you can do Max( by or Avg( by or you get the idea (by

Better reference; - https://gerardnico.com/dat/obiee/obis/logical_sql/group_by

Or - you can use sections in your table / pivot table and have a Max function at each break, then you get both the max by state and the max (of all).