Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
i have a pivot table calculation on counting invoices by month
and the expression is
Expr : Count(DISTINCT if(ORDTYPE='SL', if(IsNull(IVNO),'', IVNO)))/sum(SRDAYS)
my table looks like before.jpeg
i want the sum off all rows as total. so i have used like
Expr :
=sum(AGGR(
Count(DISTINCT if(ORDTYPE='SL', if(IsNull(IVNO),'', IVNO)))/sum(SRDAYS)
,BRCODE
,BRNAME
,RSMNAME
))
then i m not getting values for all branch in all months.
the table looks like after.jpeg.
how can i sort out the issue.
please help
Try this:
=sum(AGGR(
Count(DISTINCT if(ORDTYPE='SL', if(IsNull(IVNO),'', IVNO)))/sum(SRDAYS)
,BRCODE
,BRNAME
,RSMNAME,
CalendarMonth //YourPivoted Dimension
))
If this doesn't work, then try this:
=sum(AGGR(NoDistinct
Count(DISTINCT if(ORDTYPE='SL', if(IsNull(IVNO),'', IVNO)))/sum(SRDAYS)
,BRCODE
,BRNAME
,RSMNAME,
CalendarMonth //YourPivoted Dimension
))
Best,
Sunny
Try this:
=sum(AGGR(
Count(DISTINCT if(ORDTYPE='SL', if(IsNull(IVNO),'', IVNO)))/sum(SRDAYS)
,BRCODE
,BRNAME
,RSMNAME,
CalendarMonth //YourPivoted Dimension
))
If this doesn't work, then try this:
=sum(AGGR(NoDistinct
Count(DISTINCT if(ORDTYPE='SL', if(IsNull(IVNO),'', IVNO)))/sum(SRDAYS)
,BRCODE
,BRNAME
,RSMNAME,
CalendarMonth //YourPivoted Dimension
))
Best,
Sunny
Add your CalendarMon.... dimension to the aggr too.