Not getting Pivot Total for All

[Anonymous]

Hi Experts,

I have flag 0 and 1 for Open and Won.while using the below expression it is showing the total only for 2 case (Highlighted in pink)

if(Only(TempOpenClosedOppStatusNo)=1,

NUM(SUM({<PostingDate=,dtmAddedOn={'$(=vMaxWeekStartDate)'},

  Opp_Status={'Closed'},GoLiveFlag={1},[Probability %]=,Probability=,[Billing Start Date]={">=$(=Date(YearStart(vMaxWeekStartDate,0,4)))<=$(=Date((vMaxWeekStartDate)))"}

  >}Amount_in_USD)/ 1000000  ,'$ #,###.')*1000000

, NUM(SUM({<dtmAddedOn={'$(=vMaxWeekStartDate)'} ,Opp_Status={'Open'}

,CurrentWk={'>=70'}>} Amount_in_USD)/1000000,'$ #,###.## MM')*1000000

)

Below is the output from the pivot table,Its showing total for only Open category,Can any one pls suggest me whats wrong in the expression

In Advance

Thank you

[swuehl · Accepted solution]

That's because your condition with the ONLY() function will return NULL in the total line if there is not a unique result for ONLY(). Then it will execute the else branch of your if() statement.

I think what you want is a sum-of-rows in a pivot table, which you can achieve using advanced aggegation:

= Sum(

     Aggr(

               if(Only(TempOpenClosedOppStatusNo)=1,

                    NUM(SUM({<PostingDate=,dtmAddedOn={'$(=vMaxWeekStartDate)'},

                      Opp_Status={'Closed'},GoLiveFlag={1},[Probability %]=,Probability=,[Billing Start Date]=        {">=$(=Date(YearStart(vMaxWeekStartDate,0,4)))<=$(=Date((vMaxWeekStartDate)))"}

  >}Amount_in_USD)/ 1000000  ,'$ #,###.')*1000000

  , NUM(SUM({<dtmAddedOn={'$(=vMaxWeekStartDate)'} ,Opp_Status={'Open'}

,CurrentWk={'>=70'}>} Amount_in_USD)/1000000,'$ #,###.## MM')*1000000

   )

    ,YourChartDimensions

))

Replace YourChartDimensions with your chart dimension fields, comma separated.

[Anonymous]

Simply You are rocking .Thank you so much