11 Replies Latest reply on Feb 28, 2017 2:21 PM by Christian Berg

    Pivot view calculation going wrong

    1484404

      Hi all,

       

      I have requirement where dashboard prompt have year(Pyear_pv) and  month (Pmonth_pv) as filters.

       

      Report is as follows:

       

       

      2016, 2015 columns are populating through presentation variables (heading also through PV ).

       

      The script for 2016 column is:

       

      case when "- Shipment Period Dimensions"."Year"=('@{Pyear_pv}{2016}') then count(distinct "- Shipment Dimensions"."Power Unit GID") end

       

      Here "power unit GID" is dimension column and can have duplicate values.

       

      The script for 2015 column is:

       

      case when "- Shipment Period Dimensions"."Year"= ('@{Pyear_pv-1}{2015}') then count(distinct "- Shipment Dimensions"."Power Unit GID") end

       

      Here i need to get data for current year (selected through db prompt) and previous year.

       

      NOTE: I am using obiee 11g, cloud instance (oracle FTI instance), there is no time series feature.

       

      Problem:

       

      In table view i am getting correct value. But when i convert into pivot view then the values are not coming correct.

      The report format is pivot format. Please let me know how to achieve the correct result.

       

      Regards,

      Ram.

        • 1. Re: Pivot view calculation going wrong
          Christian Berg

          You have "Year measures" and a split by Year in terms of dimensional hierarchy/attribute? Why?

          • 2. Re: Pivot view calculation going wrong
            1484404

            Hi,

             

            Because of incorporating time series functionality (Current year and previous year).

             

            At my report level i put month as "is prompted".

             

            User can select year and month so that my report will filter based on user selection. If i select year 2016 and December the result as shown below.

             

             

            Regards,

            Ram.

            • 3. Re: Pivot view calculation going wrong
              Christian Berg

              Just function-wise the proper function to use is "FILTER" rather than CASE.

               

              Example:

               

               

              Result:

               

              So as long as the split by dimensional attribute exists (case table) this works but as soon as you drop that it breaks. That's expected.

               

              But what I still don't get conceptually: If you define precisely TWO years in your prompt...why not just use that standard functionality of having the data filtered and the dimensional attribute doing the split??

               

              • 4. Re: Pivot view calculation going wrong
                1484404

                Hi,

                 

                Please find the difference for the following two scenarios:

                 

                1. I have used measure column : shipment count.

                 

                For 2016: FILTER("- Shipment Facts"."Shipment Count" using("- Shipment Period Dimensions"."Year" in ('2016')))

                 

                2016 query gives only 2016 data.

                 

                For 2015: FILTER("- Shipment Facts"."Shipment Count" using("- Shipment Period Dimensions"."Year" in ('2015')))

                 

                2015 query gives only 2015 data (please see the table view).

                 

                The result is proper in pivot view.

                 

                2. I have used dimension column: Power unit GID.

                 

                 

                For 2016: FILTER(count(distinct "- Shipment Dimensions"."Power Unit GID") using("- Shipment Period Dimensions"."Year" in ('2016')))

                 

                2016 query is giving both data.

                 

                For 2015: FILTER(count(distinct "- Shipment Dimensions"."Power Unit GID") using("- Shipment Period Dimensions"."Year" in ('2015')))

                 

                2015 query is giving both data (2016 also).

                 

                Pivot view data is not correct.

                 

                I am not understand what is going wrong and how to correct this.

                 

                regards,

                Ram.

                • 5. Re: Pivot view calculation going wrong
                  1484404

                  one more thing ...my dashboard prompt as follows

                   

                   

                  if user select 2017 year then in my report, i have to show both selected and previous year(2016) data.

                   

                  regards,

                  ram.

                  • 6. Re: Pivot view calculation going wrong
                    Thomas Dodds

                    build your counts as measures in the fact ... that's what they are.

                    • 7. Re: Pivot view calculation going wrong
                      1484404

                      Hi all,

                       

                      Please let me know any possible solution or any workaround solution is most appreciated.

                       

                      regards,

                      ram

                      • 8. Re: Pivot view calculation going wrong
                        Mark.Thompson

                        What happens if you display the Year column going down instead of the measure labels? 

                        • 9. Re: Pivot view calculation going wrong
                          Thomas Dodds

                          I did give you a solution (not a workaround) ... you know the pivot works when using a fact (it's designed to do just that) ... what you are doing (counting dim attributes) is actually logically creating facts ... so put those in the RPD in your logical fact table (or create a logical fact table at the same LTS grain as your fact).

                          • 10. Re: Pivot view calculation going wrong
                            1484404

                            Hi,

                             

                            It's cloud instance (Oracle FTI instance) and do not have access to RPD. I have to give solution in the report only. Please let me know any other solution.

                             

                            regards,

                            ram.

                            • 11. Re: Pivot view calculation going wrong
                              Christian Berg

                              Not sure if I'm speaking chinese or some dead language but once more:

                               

                              a) If you let the DIMENSION do the splitting of the MEASURE then it will work. And not use any weird formulae.

                               

                              b) If you use FILTER - the function - you don't NEED any filters - filters as in analysis filters.

                               

                              tl;dr - this will work on any SA:

                               

                              <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">

                                 <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;A - Sample Sales&quot;">

                                    <saw:columns>

                                       <saw:column xsi:type="saw:regularColumn" columnID="c4ba977d3126195f9">

                                          <saw:columnFormula>

                                             <sawx:expr xsi:type="sawx:sqlExpression">"Time"."T05 Per Name Year"</sawx:expr></saw:columnFormula></saw:column>

                                       <saw:column xsi:type="saw:regularColumn" columnID="cca2abe630db82f67">

                                          <saw:columnFormula>

                                             <sawx:expr xsi:type="sawx:sqlExpression">"Products"."P4  Brand"</sawx:expr></saw:columnFormula></saw:column>

                                       <saw:column xsi:type="saw:regularColumn" columnID="cac797b0cd4bd6ea0">

                                          <saw:columnFormula>

                                             <sawx:expr xsi:type="sawx:sqlExpression">FILTER("Base Facts"."1- Revenue" using "Time"."T05 Per Name Year"=2014)</sawx:expr></saw:columnFormula>

                                          <saw:tableHeading>

                                             <saw:caption fmt="text">

                                                <saw:text>Base Facts</saw:text></saw:caption></saw:tableHeading>

                                          <saw:columnHeading>

                                             <saw:caption fmt="text">

                                                <saw:text>2014 Revenue</saw:text></saw:caption></saw:columnHeading></saw:column></saw:columns></saw:criteria>

                                 <saw:views currentView="0">

                                    <saw:view xsi:type="saw:compoundView" name="compoundView!1">

                                       <saw:cvTable>

                                          <saw:cvRow>

                                             <saw:cvCell viewName="titleView!1"/></saw:cvRow>

                                          <saw:cvRow>

                                             <saw:cvCell viewName="tableView!1"/></saw:cvRow></saw:cvTable></saw:view>

                                    <saw:view xsi:type="saw:titleView" name="titleView!1"/>

                                    <saw:view xsi:type="saw:tableView" name="tableView!1" scrollingEnabled="true">

                                       <saw:edges>

                                          <saw:edge axis="page" showColumnHeader="true"/>

                                          <saw:edge axis="section"/>

                                          <saw:edge axis="row" showColumnHeader="true">

                                             <saw:edgeLayers>

                                                <saw:edgeLayer type="column" columnID="c4ba977d3126195f9"/>

                                                <saw:edgeLayer type="column" columnID="cca2abe630db82f67"/>

                                                <saw:edgeLayer type="column" columnID="cac797b0cd4bd6ea0"/></saw:edgeLayers></saw:edge>

                                          <saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view></saw:views></saw:report>