Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Previous quarter value in variable

Received Response
233
Views
54
Comments
1356

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod 
    3612030 wrote:Robert - again didnt worked, still no results.

    For example, using your suggestion about AGO function, this didnt work for me because of "Unresolved level" error - AGO("Historical Pipeline Facts"."Closed Opportunity Revenue", "@{QUARTER}", 1)In fact everything that I include in this function throws me this error, no matter the column.I would really appreciate a much simpler solution to this problem

    That's not the syntax of the AGO function, you will never use your presentation variable in there as it doesn't matter. You need to pass the name of the level representing the quarters in your time dimension. Do you have a time dimension, right? Look at the doc of the AGO function and write it properly.

    More in general I have the feeling the answer is in @Robert Angel question you are turning around without answering : in the dataset are both condition TRUE on the same line of data? Because that's how conditions in SQL works, at the row level, row by row. It isn't a set operation like "take all the rows matching the first conditions and add all the rows matching the second condition".

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Thanks for aiding on the understanding front Gianni - I have a feeling @3612030 (have you thought of making the display name more user friendly, it can still be anonymous?) is looking for the formula filter functionality that I briefly alluded to, to get KPI type functionality in a measure without the row by row situation 'limitation'.

    @3612030 - please see below and try. Note you can filter(filter (....) .....)

    pastedImage_1.png

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    pastedImage_0.png

    3612030 wrote:I would really appreciate a much simpler solution to this problem

    Well the simpler solution is to do things correctly. If one keeps chopping off heads with a butterknife because the patient complains of headache although the first counsel is "take an aspirin" one can ask for as many "simpler solutions" as one wants - if you don't TAKE the simpler solution and keeps asking with which instrument to replace the butterknife...well you'll always have the same result.

    There's a reason analytical tools have a proper notion of time dimensions and time series calculations and do not base their "time logic" on convoluted code mashing.

  • 3612030
    3612030 Rank 3 - Community Apprentice

    Ok seems like I get it now, in one row the conditions together are not possible. That is why I propably dont get any results.

    Robert - the filter option with for example:

    FILTER("Historical Pipeline Facts"."Closed Opportunity Revenue" USING ("Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter" = (CASE WHEN  RIGHT('@{QUARTER}', 1) = '1'  THEN CAST(CAST(LEFT('@{QUARTER}', 4) AS INTEGER) -1 AS CHAR)||' '||'Q'||' '||'4' ELSE CAST(CAST(LEFT('@{QUARTER}', 4) AS INTEGER) AS CHAR)||' '||'Q'||' '||CAST(CAST(RIGHT('@{QUARTER}',1) AS INTEGER)-1 AS CHAR) END)))

    also didnt get me a results.

    Back in to my question then - I didnt find any examples of how to use AGO function in combination with dynamic variable of Quarter. That is why Im just asking for any code example, I could handle myself from that point.

  • Gianni Ceresa
    Gianni Ceresa Mod 
    3612030 wrote:Back in to my question then - I didnt find any examples of how to use AGO function in combination with dynamic variable of Quarter. That is why Im just asking for any code example, I could handle myself from that point.

    AGO couldn't care less about your variable quarter

    You use your variable to filter in the criteria tab, so just a filter saying "your quarter column" = your variable. Then you add a column and in the column formula you use AGO.

    AGO will works based on the filters of the analysis itself, and what it requires is to know at which level of the time dimension you want to be moving in the past and the number of periods you want to go back.

    So the question is all about if you have a properly setup time dimension. If you do look for the name of the level (not column, the level of the dimension) representing the quarter.

    If you don't have a proper time dimension it means 2 things: you must forget about AGO and your model is rubbish (sorry but ....)

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    On didn't get me results, did you remove your external filters?

  • SonPat99
    SonPat99 Rank 6 - Analytics Lead

    Dear User,

    Please try this:

    1. Create another dashboard prompt

    2. Write a SQL using your logic "CASE WHEN  RIGHT('@{QUARTER}', 1) = '1'  THEN CAST(CAST(LEFT('@{QUARTER}', 4) AS INTEGER) -1 AS CHAR)||' '||'Q'||' '||'4' ELSE CAST(CAST(LEFT('@{QUARTER}', 4) AS INTEGER) AS CHAR)||' '||'Q'||' '||CAST(CAST(RIGHT('@{QUARTER}',1) AS INTEGER)-1 AS CHAR) END"  to populate previous quarter.

    3. Define a new Presentation Variable in the new Prompt

    4. Test if the new Prompt is populating values of the previous Quarter as per your requirement.

    5. Hide this prompt post keeping it on the same dashboard.

    You can now use this new Presentation variable to filter out records on basis of Previous Quarter.

    I'm not sure if this logic is "Simpler" for you or complicated, but this should definitely work.

    Note: Please add a narrative/text view to verify if your variables are getting populated correctly.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    See also; -

    pastedImage_0.png

    So here you will substitute your quarter equivalent in your time hierarchy where the below has time.time.year, the 1 is for the number of quarters you want to go back, the first part is the measure you are looking back to the previous time based unit on.

    As Gianni / Christian have pointed out if your time hierarchy is not fit for purpose then AGO (and other time series functions) will not work for you, but if your time hierarchy is not fit for purpose then that is the least of your troubles.

  • 3612030
    3612030 Rank 3 - Community Apprentice

    This time I used the AGO function correctly but got an error " [nQSError: 14078] This query requires the External Expression to be set in the physical Complex Join Fact_CrmOpportunityAM_RevenueTimeBasedSnapshot_Summary_Quarter_Dim_CrmAnalyticsAM_CrmAnalyticsModelAM_CrmFiscalDayPVO_Common. (HY000)"

    And then I found on the forum that AGO function doesnt work in Cloud Instances. -

    Propably posted it on the wrong section then.

    So I guess I will be using the suggestion of SonPat99

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    If the AND logic still applied, and putting explicit filters on does not return any rows then SonPat99's suggestion will not work either - your logic is flawed.

    Removing the external filters and using the filter(filter logic should -  provided there is data that satisfies both conditions you are putting on the aggregated data set.