3 Replies Latest reply: Feb 2, 2012 11:04 AM by Jakub Michalik RSS

    Pivot Table

      Hi

      I have a pivottable with an expression population and calls. If i add  a dimension Period the calls should separated by Period but the population

      should not change! See attached excel sheet.

       

      Do you have any iedas?

      Thanks

      Juri

        • Pivot Table
          Jakub Michalik

          Maybe use this:

          Count (DISTINCT {$<Jahr=,Monat=,PROD=,OWNERLOGIN=,Periode=,DETAIL=,Kontakttyp=>} TOTAL<New_Area> CID)

          for the Population expression. This will make the expression ignore all chart dimensions except New_area (as this is the one you want to keep) .

            • Re: Pivot Table

              Thanks! Works perfectly! One more thing! Because only 2 Periods have Calles in but because of the population he now shows all the periods. How

               

              Can I suppress  Periods without Calls?

               

              Thanks

               

              Juri

                • Pivot Table
                  Jakub Michalik

                  Like that:

                  If(Count (DISTINCT TOTAL<Periode> ROW_ID)>0,Count (DISTINCT {$<Jahr=,Monat=,PROD=,OWNERLOGIN=,Periode=,DETAIL=,Kontakttyp=>} TOTAL<New_Area> CID))

                   

                  Basically, the condition is Calls expression, with TOTAL<Periode> added just in case there is a New_Area with no calls in some period (presumably you wouldn't like to show nothing for Population in that case). If you can be absolutely sure there will be no cases like that, TOTAL<Periode> is not necessary and you coud write it like this:

                   

                  If([Calls]>0,Count (DISTINCT {$<Jahr=,Monat=,PROD=,OWNERLOGIN=,Periode=,DETAIL=,Kontakttyp=>} TOTAL<New_Area> CID))