5 Replies Latest reply: Oct 5, 2015 8:38 AM by Jonathan Poole RSS

    Pivot table aggregation (Gesamtwerte)

      Good morning,

       

      I attached a Picture because it's easier to say what my Problem is. In my Pivot table i can't Switch my Aggregate value of my Pivot table to the right side. Although I know this Features exists and I have seen it before.

       

      Can you help me?

       

      Cheers

      Matthias

      Gesamtwerte.PNG

        • Re: Pivot table aggregation (Gesamtwerte)
          Jonathan Poole

          i've done a small extra table load in the data model to include a Totals bucket in the original dimension in order to achieve this.

           

          If the Columns were Years for example, you can associate a new dimension 'YearDimension' with each Year and associate a 'Total' value with all the Years as follows. Then swap Year for YearDimension in the chart and optionally use the YearDimensionSortOrder to customize the sorting of the YearDimension values

           

           

          YearwithTotals:

          Load

            Year as Year,

              Year as YearDimension,

              Year as YearDimensionSortOrder

          resident Dates;   

           

          concatenate(YearwithTotals)

          Load

            Year as Year,

              'Total' as YearDimension,

              1 as YearDimensionSortOrder

          resident Dates;   

           

          Untitled.png

            • Re: Pivot table aggregation (Gesamtwerte)

              Hi Jonathan,

               

              thank's for your Input but as I mainly load via SQL Statements i have some questions about your Load script.

               

              If you want I can send you my SQL Statement which creates my Revenue table. In my case I would use Month instead of years and total all the month of the year. Nonetheless that should work.

               

              I put your Load script above my Revenue Statement but it always says "Table not found monthwithtotals"

               

              Can you give me further hints?

               

              Thank you

               

              Matthias

                • Re: Pivot table aggregation (Gesamtwerte)
                  Jonathan Poole

                  can you share the script ?  i'm guessing this is occurrng during the concatenate step and its likely a typo or mixed upper/lower case thing with the table name.

                    • Re: Pivot table aggregation (Gesamtwerte)

                      Hello Jonathan,

                       

                      thanks for your help. As I cannot upload the SQL data I paste the script in here. Maybe you can identify why I cannot work with your Load script.

                       

                       

                      LIB CONNECT TO 'ELA-SQL4 (container_matthiasb)';
                      Qualify *;
                      Unqualify AB_ADM, Kundenname, Kundennummer, AB_Datum, Plz2, Monat, Tag;
                      Load *,
                      Month(BuchDatum) as Monat,
                      Year(BuchDatum) as Jahr,
                      Day(BuchDatum) as Tag,
                      'Q' & ceil(month(BuchDatum)/3) as Quartal,
                      Floor(BuchDatum) as DateNum1,
                      GeoMakePoint(Latitude, Longitude) as LocationGLE;
                      UmsatzGLE:
                      SELECT
                      Res.Auftraggeber as Kundennummer
                      ,Res.[Firma] as Kundenname
                      ,Res.[Firma] as Kundenname2
                      ,Res.[Strasse]
                      ,Res.[PLZ] as Plz
                      ,substring(Res.[PLZ],0,3) as Plz2
                      ,Res.[Ort]
                      ,Res.[Land]
                      ,Res.[GLKonto]
                      ,Res.[DimKTR] as Vertragsnummer
                      ,Res.[Baustellennummer] as BauCode
                      ,Res.[Bauname]
                      ,Res.[Longitude]
                      ,Res.[Latitude]
                      ,Res.[DimVertrGeb] as VertriebsGeb
                      ,Res.[DimADM] as AB_ADM
                      ,Res.[Beleg]
                      ,Res.[Hauptknoten] as Hauptknoten
                      ,Res.[Knoten] as Knoten
                      ,CONVERT(date,Res.[BuchDatum]) as BuchDatum
                      ,Res.[Betrag]
                      FROM (
                      Select
                         case when RH.[Rent-to Customer No_]  is not null then RH.[Rent-to Customer No_]
                               when RHA.[Rent-to Customer No_]  is not null then RHA.[Rent-to Customer No_]
                               when SH.[Sell-to Customer No_] is not null then SH.[Sell-to Customer No_]
                               when SHA.[Sell-to Customer No_] is not null then SHA.[Sell-to Customer No_]
                               when CU.[No_] is not null then CU.[No_] else 'NA' end as Auftraggeber
                         , case when RH.[Rent-to Contact No_] is not null then RH.[Rent-to Contact No_]
                               when RHA.[Rent-to Contact No_] is not null then RHA.[Rent-to Contact No_]
                               when SH.[Sell-to Contact No_] is not null then SH.[Sell-to Contact No_]
                               when SHA.[Sell-to Contact No_] is not null then SHA.[Sell-to Contact No_]
                         else 'NA' end as KontNr
                         , case when RH.[Rent-to Customer No_] is not null then RH.Name
                               when RHA.[Rent-to Customer No_] is not null then RHA.Name
                               when SH.[Sell-to Customer No_] is not null then SH.[Name]
                               when SHA.[Sell-to Customer No_] is not null then SHA.[Name]
                               when CU.[No_] is not null then CU.[Name] else 'NA' end as Firma
                         , case when RH.[Rent-to Customer No_] is not null then RH.[Address]
                               when RHA.[Rent-to Customer No_] is not null then RHA.[Address]
                               when SH.[Sell-to Customer No_] is not null then SH.[Address]
                               when SHA.[Sell-to Customer No_] is not null then SHA.[Address]
                               when CU.[No_] is not null then CU.[Address] else 'NA' end as Strasse
                         , case when RH.[Rent-to Customer No_] is not null then RH.[Post Code]
                               when RHA.[Rent-to Customer No_] is not null then RHA.[Post Code]
                               when SH.[Sell-to Customer No_] is not null then SH.[Post Code]
                               when SHA.[Sell-to Customer No_] is not null then SHA.[Post Code]
                               when CU.[No_] is not null then CU.[Post Code] else 'NA' end as PLZ
                         , case when RH.[Rent-to Customer No_] is not null then RH.[City]
                               when RHA.[Rent-to Customer No_] is not null then RHA.[City]
                               when SH.[Sell-to Customer No_] is not null then SH.[City]
                               when SHA.[Sell-to Customer No_] is not null then SHA.[City]
                               when CU.[No_] is not null then CU.[City] else 'NA' end as Ort
                         , case when RH.[Rent-to Customer No_] is not null then RH.[Country_Region Code]
                               when RHA.[Rent-to Customer No_] is not null then RHA.[Country_Region Code]
                               when SH.[Sell-to Customer No_] is not null then SH.[Country_Region Code]
                               when SHA.[Sell-to Customer No_] is not null then SHA.[Country_Region Code]
                               when CU.[No_] is not null then CU.[Country_Region Code] else 'NA' end as Land
                         , case when RH.[Rent-to Customer No_] is not null then 'Miete'
                               when RHA.[Rent-to Customer No_] is not null then 'Miete'
                               when SH.[Sell-to Customer No_] is not null then 'Verkauf'
                               when SHA.[Sell-to Customer No_] is not null then 'Verkauf'
                               when CU.[No_] is not null then 'NIX' else 'NA' end as AuftragBeleg
                         , GLE.[G_L Account No_] as GLKonto
                         , case when GLA.[Acc_ Schedule Line 2] Between 130000 and 140000 then 'Mieterlöse'
                             when GLA.[Acc_ Schedule Line 2] Between 190000 and 200000 then 'Verkaufserlöse'
                          when GLA.[Acc_ Schedule Line 2] Between 232500 and 242500 then 'Service Erlöse'
                          when GLA.[Acc_ Schedule Line 2] Between 282500 and 292500 then 'Sonstige Erlöse'
                          when GLA.[Acc_ Schedule Line 2] Between 312500 and 312500  then 'Anlagenverkäufe'
                          else 'NA' end as Hauptknoten
                      , convert(varchar(50),substring(ASL.[Description],4,50)) as Knoten
                         , GLA.Name
                         , GLE.[Source Code] as Herkunft, GLE.[Source No_] as HerkKo
                         , case when DimKTR.[Dimension Value Code] is null then 'NA' else DimKTR.[Dimension Value Code] end as DimKTR
                         , case when DimPRJ.[Dimension Value Code] is null then 'NA' else DimPRJ.[Dimension Value Code] end as DimPjNr
                         , case when RH.[Site Code] is not null then RH.[Site Code]
                               when RHA.[Site Code] is not null then RHA.[Site Code]
                               when SH.[Site Code] is not null then SH.[Site Code]
                               when SHA.[Site Code] is not null then SHA.[Site Code]
                               when CU.[No_] is not null then 'NA' else 'NA' end as Baustellennummer
                      ,STS.[Name] as Bauname
                      , case when STS.[Longitude] = 0 then 9.3431 else STS.[Longitude] end as Longitude
                      , case when STS.[Latitude] = 0 then 51.3389 else STS.[Latitude] end as Latitude
                         , case when DimART.[Dimension Value Code] is null then 'NA' else DimART.[Dimension Value Code] end as DimArtTYP
                         , case when DimVTG.[Dimension Value Code] is null then 'NA' else DimVTG.[Dimension Value Code] end as DimVertrGeb
                         , case when DimADM.[Dimension Value Code] is null and DimVTG.[Dimension Value Code] = 'UNITS' then 'UNITS'
                                when DimADM.[Dimension Value Code] is null and DimVTG.[Dimension Value Code] is null then 'NA'
                                else DimADM.[Dimension Value Code] end as DimADM  
                         , GLE.[Document No_] as Beleg
                         , case when RH.[Order Date] is not null then convert(varchar(10),RH.[Order Date],20)
                                when RHA.[Order Date] is not null then convert(varchar(10),RHA.[Order Date],20)
                                when SH.[Order Date] is not null then convert(varchar(10),SH.[Order Date],20)
                                when SHA.[Order Date] is not null then convert(varchar(10),SHA.[Order Date],20)
                                when CU.[No_] is not null then 'NIX' else 'NA' end as AuftragDatum
                         , convert(varchar(10),GLE.[Posting Date],20) as BuchDatum, GLE.[Amount]*(-1) as Betrag  
                         , GLE.[Description] as BuchText
                         From [ELA Container GmbH$G_L Entry] GLE
                         outer apply (select top(1) TRH.[Rent-to Customer No_], TRH.[Rent-to Contact No_], TRH.[Order Date]
                                        , CU_RH.Name, CU_RH.Address, CU_RH.[Post Code], CU_RH.City, CU_RH.[Country_Region Code], TRH.[Site Code]
                                     from [ELA Container GmbH$Rental Header] TRH
                                     inner join [ELA Container GmbH$Customer] CU_RH ON CU_RH.No_ = TRH.[Rent-to Customer No_]
                                     where TRH.No_ = GLE.[Global Dimension 2 Code] and TRH.[Document Type] = 4 ) as RH

                         outer apply (select top(1) TRHA.[Rent-to Customer No_], TRHA.[Rent-to Contact No_], TRHA.[Order Date]
                                        , CU_RHA.Name, CU_RHA.Address, CU_RHA.[Post Code], CU_RHA.City, CU_RHA.[Country_Region Code], TRHA.[Site Code]
                                     from [ELA Container GmbH$Rental Header Archive] TRHA
                                     inner join [ELA Container GmbH$Customer] CU_RHA ON CU_RHA.No_ = TRHA.[Rent-to Customer No_]
                                     where TRHA.No_ = GLE.[Global Dimension 2 Code] and TRHA.[Document Type] = 4
                                     order by [Version No_] desc) as RHA

                         outer apply (select top(1) TSH.[Sell-to Customer No_], TSH.[Sell-to Contact No_], TSH.[Order Date]
                                        , CU_SH.Name, CU_SH.Address, CU_SH.[Post Code], CU_SH.City, CU_SH.[Country_Region Code], TSH.[Site Code]
                                     from [ELA Container GmbH$Sales Header] TSH
                                     inner join [ELA Container GmbH$Customer] CU_SH ON CU_SH.No_ = TSH.[Sell-to Customer No_]
                                     where TSH.No_ = GLE.[Global Dimension 2 Code] and TSH.[Document Type] = 1 ) as SH

                         outer apply (select top(1) TSHA.[Sell-to Customer No_], TSHA.[Sell-to Contact No_],TSHA.[Order Date]
                                        , CU_SHA.Name, CU_SHA.Address, CU_SHA.[Post Code], CU_SHA.City, CU_SHA.[Country_Region Code], TSHA.[Site Code]
                                     from [ELA Container GmbH$Sales Header Archive] TSHA
                                     inner join [ELA Container GmbH$Customer] CU_SHA ON CU_SHA.No_ = TSHA.[Sell-to Customer No_]
                                     where TSHA.No_ = GLE.[Global Dimension 2 Code] and TSHA.[Document Type] = 1
                                     order by [Version No_] desc) as SHA

                         left outer join [ELA Container GmbH$G_L Account] GLA on GLA.No_ = GLE.[G_L Account No_]
                         left outer join [ELA Container GmbH$Customer] CU    ON CU.No_    = GLE.[Source No_]
                         left outer join [ELA Container GmbH$Chain Name] UK on UK.Code = CU.[Chain Name]
                         left outer join [ELA Container GmbH$Ship-to Site] STS on STS.[Code] = RH.[Site Code] or STS.[Code] = SH.[Site Code] or STS.[Code] = RHA.[Site Code] or STS.[Code] = SHA.[Site Code]
                         left outer join [ELA Container GmbH$Acc_ Schedule Line] ASL on GLA.[Acc_ Schedule Line 2] = ASL.[Line No_] and GLA.[Acc_ Schedule Name 2] = ASL.[Schedule Name]
                         left outer join [ELA Container GmbH$Ledger Entry Dimension] DimKTR ON DimKTR.[Entry No_]=GLE.[Entry No_] and DimKTR.[Table ID] = 17 and DimKTR.[Dimension Code] = 'KTR'
                         left outer join [ELA Container GmbH$Ledger Entry Dimension] DimPRJ ON DimPRJ.[Entry No_]=GLE.[Entry No_] and DimPRJ.[Table ID] = 17 and DimPRJ.[Dimension Code] = 'ELA-PROJEKT'
                         left outer join [ELA Container GmbH$Ledger Entry Dimension] DimART ON DimART.[Entry No_]=GLE.[Entry No_] and DimART.[Table ID] = 17 and DimART.[Dimension Code] = 'ARTIKEL-TYP'
                         left outer join [ELA Container GmbH$Ledger Entry Dimension] DimVTG ON DimVTG.[Entry No_]=GLE.[Entry No_] and DimVTG.[Table ID] = 17 and DimVTG.[Dimension Code] = 'VTA-GEBIET'
                         left outer join [ELA Container GmbH$Ledger Entry Dimension] DimADM ON DimADM.[Entry No_]=GLE.[Entry No_] and DimADM.[Table ID] = 17 and DimADM.[Dimension Code] = 'VTA-ADM'

                      Where ( (len(GLE.[G_L Account No_]) = 5 and (GLE.[G_L Account No_] Between '40000'    And '42200'
                                     Or GLE.[G_L Account No_] Between '42205'    And '43999'
                                     Or GLE.[G_L Account No_] Between '45000'    And '45999'
                                     Or GLE.[G_L Account No_] Between '46110'    And '46189'
                                      Or GLE.[G_L Account No_] Between '46260'    And '46263'
                                  Or GLE.[G_L Account No_] Between '48503'    And '48503'
                                     Or GLE.[G_L Account No_] Between '49000'    And '49014'
                                     Or GLE.[G_L Account No_] Between '49016'    And '49999'))
                      or (len(GLE.[G_L Account No_]) = 8  and (GLE.[G_L Account No_] Between '41100000' And '43369999'
                                     Or GLE.[G_L Account No_] Between '43370001' And '45051002'
                                     Or GLE.[G_L Account No_] Between '45052003' And '45052003'
                                  Or GLE.[G_L Account No_] Between '45053010' And '45053010'
                                  Or GLE.[G_L Account No_] Between '45054015' And '45054015'
                                  Or GLE.[G_L Account No_] Between '47300000' And '47999999'
                                  Or GLE.[G_L Account No_] Between '47600100' And '47600100'
                                  Or GLE.[G_L Account No_] Between '47694002' And '47694002'
                                     Or GLE.[G_L Account No_] Between '48320102' And '48320102'
                                     Or GLE.[G_L Account No_] Between '48450100' And '48450300'
                                     Or GLE.[G_L Account No_] Between '48470202' And '48470303'
                                  Or GLE.[G_L Account No_] Between '48470515' And '48470515'
                                     Or GLE.[G_L Account No_] Between '49700000' And '49700102'
                                  Or GLE.[G_L Account No_] Between '57410001' And '57410001'
                                     Or GLE.[G_L Account No_] Between '68850100' And '68850200'
                                  Or GLE.[G_L Account No_] Between '68870115' And '68870115'
                                  Or GLE.[G_L Account No_] Between '68870202' And '68870202')) )

                         and GLE.[Source Code] <> 'JAHRABSCH'
                        
                         and convert(varchar(10),GLE.[Posting Date],120) Between convert(date,DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR,-1,GETDATE())), 0)) and convert(date,DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1))
                      ) Res;

                        • Re: Pivot table aggregation (Gesamtwerte)
                          Jonathan Poole

                          There is a single table load in the statement above and it doesn't seem to resemble my logic above.

                           

                          Perhaps you can reapply the technique, recreate the error and then send me the full load script that is causing the error. You can paste it here as you did above or copy into a txt file and attach it to your thrread. If you open the full thread (and not just a reply) you have the option of replying with 'use advanced editor' where you can upload a file.