This discussion is archived
12 Replies Latest reply: Mar 20, 2013 5:44 AM by Satya Ranki Reddy RSS

issue with qtd and ytd

976632 Newbie
Currently Being Moderated
Hi

My hierarchy is year-quarter-month

i gave chronological key for all levels

let us suppose if i have select Aug-12 then qtd sum give that value only it is take month values based on alphanumeric (it takes start with 'a as first month like this)
'
for quarter ago also it gives previous year quarters




for long time im work on this but i didnt get this

please help any one
  • 1. Re: issue with qtd and ytd
    980674 Newbie
    Currently Being Moderated
    Hi,

    Can you please explain how you have set the MTD and QTD fields in the BMM? Also, please give the structure of your time dim and the fields used for each chronological key.

    I think the issue is with the way you have set up the hierarchy. If you can let me know how you have set it up, I would be able to help you better.

    Thanks
    Gayathri
  • 2. Re: issue with qtd and ytd
    976632 Newbie
    Currently Being Moderated
    Hi

    Thanks for ur reply

    my hierarchy is

    year------------year filed values---2011,2012,2013...
    quarter-------quarter filed values---q1-2011,q2-2011,q3-2011,q4-2011,....
    month -------month values------Jan-11,feb-11,mar-11,apr-11,jun-11,....

    i set chronological key for all levels year ,quarter,month

    i created qtd----todate(0

    quarterago----ago()

    ytd---todate()-year level



    functions are correct
    ur correct that hierarchy has problem

    i sort the monthname field by startdate

    i dont know why the values takes like ascii format(a,b,c,d, ...)this format for both quarter and monthname


    please can you suggest me in this

    Thanks
    Brahma
  • 3. Re: issue with qtd and ytd
    Satya Ranki Reddy Expert
    Currently Being Moderated
    Hi,

    It sounds like you are on the right track. The lack of some fact records for specific dates should not matter. The Time Series Functions in the RPD are totally reliant on the time dimension being setup correctly, so I would first suggest checking the time dimension (hierarchy) setup. A common problem is with level-based keys. Check that keys have been defined correctly at each hierarchy level (except the Grand Total level); make sure that you have a sequential chronological key* defined at each dimension level, primary and drill down keys have been defined at each level (as required) and the grand total level has been correctly flagged. Don't forget, the dimension needs to be flagged as a time dimension too! There is plenty of guidance on the web about setting up and using time dimensions if you need more detail.

    * If you think about this laterally, the time series functions need to know how to work out what a measure value was x periods ago. Periods could be years, months, weeks, quarters; as defined by the level variable in the time series function. So, based on a prompted period, say Mar-2012, the BI server does not have any inbuilt calendar logic that knows that two periods ago it was Jan-2012. Our calendar could be defined using some strange period names e.g JN10-11, FB10-11, MR11-12 etc. We need to tell the BI server how these periods are sequentially (or chronalogically) related. This is where the chronological keys come in which we define in the dimension.

    Also, ensure that the aggregation method of the base measure is Sum.

    Check the syntax you are using for the time series function:-

    AGO(measure, time dimension level, number of period(s) ago) e.g. AGO(Core."Fact - Project Cost".Cost, Core."Date - Fiscal Calendar"."Fiscal Period", 1)

    Do you get any RPD consistency check errors? Have you created a logical join between the fact and the dimension tables in the business model?

    Finally, have you looked at the physical SQL being generated to get some pointers? You can do this by ensuring that the log level variable is set appropriately to display physical SQL in the query logs (e.g. level 2).

    Plese refer the below link for more details.

    http://satyaobieesolutions.blogspot.in/2012/09/obiee-11g-time-series-functions.html

    Hope this help's

    Thanks
    Satya
  • 4. Re: issue with qtd and ytd
    980674 Newbie
    Currently Being Moderated
    Hi Brahma,

    Try creating two additional fields for quarter and month in the format YYYYQQ and YYYYMM. Both should be numerical fields and use these fields as your chronological keys.
    Please let me know if this helps you.

    Thanks
    Gayathri
  • 5. Re: issue with qtd and ytd
    976632 Newbie
    Currently Being Moderated
    Hi

    Thanks for your valueable information

    im using logical join between fact and times table in bmm layer

    is this any problem


    i have another suspect

    first time i created hierarchy year-quarter-month

    later i want to change the hierarchy bur it is not supposed to change the hierarchy

    that means if i want to change the key column in any level then it give consistency error

    why it happens like this
  • 6. Re: issue with qtd and ytd
    Satya Ranki Reddy Expert
    Currently Being Moderated
    Hi Arya,

    I guess your in confusion mode.

    Please cross check your time dim hierarchy and chronological key and level.

    I can suggest you please take rpd in your local system then create new dim hierarchy and define the keys check consistency error. This way you will find the issue easily.

    Hope this help's,

    Thanks
    Satya
  • 7. Re: issue with qtd and ytd
    Satya Ranki Reddy Expert
    Currently Being Moderated
    Hi Arya,

    I guess your in confusion mode.

    I can suggest you please cross check your time hierarchy and chronological key and level.

    Best solution for you, take rpd in your location system then try to rebuild and check the consistency error. You will get exact issue.


    Hope this help's

    Thanks
    Satya
  • 8. Re: issue with qtd and ytd
    976632 Newbie
    Currently Being Moderated
    Hi

    thanks for ur reply


    thats ok,i didnt get consistency check error

    but i didnt get monthnames orderformat

    it takes like Aug-11,Apr-11,Dec-11,...like this

    my month values are taken as ascii format(a,b,c,d...)

    for exm--for 1st quarter it tales feb as first month , jan as next ,march as next like this

    mainly todate() and ago functions have problem for this

    functions work correctly
    but data is not correct it is based on ascii format

    please help me in this

    Edited by: ARYABRAHMA on Mar 20, 2013 4:10 AM
  • 9. Re: issue with qtd and ytd
    976632 Newbie
    Currently Being Moderated
    my data is like this

    quarter month amount qtd amount qago
                   


                   


                   


                   


         

    Quarter          
                   
              
              Qago
    quarter     Period name Net amount     QTD           
         
    Q1-2011     JAN-11     536100.00      1300578.87     
         FEB-11     764478.87      764478.87     
         MAR-11     782214.21      2082793.08     
    Q2-2011     APR-11     815363.61      815363.61     
         MAY-11     805367.12     2725282.52     
         JUN-11     1104551.79     1919915.40     
    Q3-2011     JUL-11     835386.90     1674954.94     
         AUG-11     839568.04      839568.04     
         SEP-11     861497.21     2536452.15     
    Q4-2011     OCT-11     66542.59     2330157.05     
         NOV-11     1382129.01     2263614.46     
         DEC-11     881485.45     881485.45     
    Q1-2012     JAN-12     94633.15     4760049.24     2082793.08
         FEB-12     4665416.09     4665416.09     2082793.08
         MAR-12     103171.38     4863220.62     2082793.08
    Q2-2012     APR-12     86618.34     86618.34     2725282.52
         MAY-12     85079.62     243996.08     2725282.52
         JUN-12     72298.12     158916.46     2725282.52
    Q3-2012     JUL-12     59328.51     111524.33      2536452.15
         AUG-12     52195.82     52195.82     2536452.15
         SEP-12     560319.49      671843.82      2536452.15
    Q4-2012     OCT-12     1491105.39     21534004.23     2330157.05
         NOV-12     19780107.55     20042898.84     2330157.05
         DEC-12     262791.29     262791.29     2330157.05
    Q1-2013     JAN-13     257640.02     490347.12     4863220.62

    Rows 1 - 25



    can see in the qtd and qago data

    Edited by: ARYABRAHMA on Mar 20, 2013 4:29 AM
  • 10. Re: issue with qtd and ytd
    Satya Ranki Reddy Expert
    Currently Being Moderated
    Hi Arya,

    I understood, Do one thing in your Time Dim tables having one time key column value like(20110121). If you having this column apply chronological key in this column that to lowest level and check it.

    Note: Don't define chronological key each and every level just define only lowest level. Ex(Date column (or) Time key column).

    Please do the above steps and update the post.

    Thanks,
    Satya
  • 11. Re: issue with qtd and ytd
    976632 Newbie
    Currently Being Moderated
    Hi

    Im using start date for lowest level that is in timestamp format this level is now newly added

    then i get the below error


    ERRORS:


    Business Model REVPROOBIEE:
    [nQSError: 15019] Table Periods is functionally dependent upon level Month, but a more detailed child level has associated columns from that same table or a more detailed table.
    [nQSError: 15001] Could not load navigation space for subject area REVPROOBIEE.
  • 12. Re: issue with qtd and ytd
    Satya Ranki Reddy Expert
    Currently Being Moderated
    Hi,

    Refer this links you can reslove this issue.

    http://gerardnico.com/wiki/dat/obiee/15019

    http://mtalavera.wordpress.com/2012/07/17/obiee-rpd-consistency-error-nqserror-15019-table-is-functionally-dependent-upon-level/


    Hope this help's

    Thanks,
    Satya

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points