topic Re: Not getting Pivot Total for All in QlikView

Not getting Pivot Total for All

Anonymous — Tue, 17 Nov 2015 12:33:58 GMT

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

Re: Not getting Pivot Total for All

swuehl — Tue, 17 Nov 2015 14:24:21 GMT

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.

Re: Not getting Pivot Total for All

Anonymous — Wed, 18 Nov 2015 06:16:27 GMT

Simply You are rocking .Thank you so much