12 Replies Latest reply: Mar 20, 2013 7:44 AM by Satya Ranki Reddy RSS

    issue with qtd and ytd


      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

          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.

          • 2. Re: issue with qtd and ytd

            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


            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

            • 3. Re: issue with qtd and ytd
              Satya Ranki Reddy

              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.


              Hope this help's

              • 4. Re: issue with qtd and ytd
                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.

                • 5. Re: issue with qtd and ytd

                  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
                    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,

                    • 7. Re: issue with qtd and ytd
                      Satya Ranki Reddy
                      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

                      • 8. Re: issue with qtd and ytd

                        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
                          my data is like this

                          quarter month amount qtd amount 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
                            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.

                            • 11. Re: issue with qtd and ytd

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

                              then i get the below error


                              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

                                Refer this links you can reslove this issue.



                                Hope this help's