aggr calculation

vikasmahajan — Thu, 21 Nov 2013 07:02:08 GMT

Dear all

Please refer attachment of jpg file showing sales with Region count in dimension now

I want to calculate sales but not using rmcount dimension ,

Using AGGR how to calculate sales

RM	Rmcount	sale
Total		49.60
BANGALORE - 1 ABM	4	6.60
BANGALORE - 1 ABM	1	1.58
BANGALORE - 2 ABM	5	8.25
BANGALORE - 2 ABM	1	3.97
BANGALORE - 3 ABM	4	6.60
BANGALORE - 3 ABM	1	3.31
BANGALORE - 4 ABM	5	8.25
MYSORE ABM	1	6.20
MYSORE ABM	2	4.84

My formula is

if( Rmcount > 1,

((SUM(INV_INVVAL)-SUM(SRN_INVVAL))/100000)*Rmcount,

(SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},

[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}>}[Gross Sales Amount])

SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},

[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}>}[Sales Return Amount])

)/100000

)

THANKS

VIKAS

Re: aggr calculation

vikasmahajan — Thu, 21 Nov 2013 09:01:38 GMT

Resolved by using

Sum(aggr( sum(sales),dimensions))

// sum(aggr(if(expressoin2>1,sum(Venta * REBATE_NORMAL),0),dim1,dim2))

Thanks

topic aggr calculation in QlikView

aggr calculation

Re: aggr calculation