topic Re: Add AGGR function in QlikView

Add AGGR function

Tue, 03 May 2016 13:20:50 GMT

I need to add the aggr function to the expression below to give a column total in my pivot table for the dimension called 'Region'

Sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Salesquantity1 )

- sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} SalesQtyOld)

+ sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} SalesQtyNew))

I'm not sure how to use this function

Re: Add AGGR function

settu_periasamy — Tue, 03 May 2016 13:23:42 GMT

Maybe try

=Sum (Aggr (above mentioned expression ,Region))

Re: Add AGGR function

swuehl — Tue, 03 May 2016 13:24:00 GMT

If you need sum-of-rows in a pivot table, you can do it like

=Sum(

Aggr(

YourCurrentExpression,

ChartDimension1,

ChartDimension2,

ChartDimension3

)

Replace YourCurrentExpression and the ChartDimensionsX with your expression and all chart dimension (fields only).

Re: Add AGGR function

marcus_sommer — Tue, 03 May 2016 13:25:14 GMT

You could try this:

sum(aggr(

, Region))

- Marcus

Re: Add AGGR function

Tue, 03 May 2016 13:29:07 GMT

The expression looks ok but I just get zero in the results and no total

Re: Add AGGR function

robert_mika — Tue, 03 May 2016 13:31:51 GMT

Did you check them one by one to see which one fails?

Re: Add AGGR function

marcus_sommer — Tue, 03 May 2016 13:34:28 GMT

What dimensions are included in this chart? And check the syntax of your expression - I have only copy/paste your snippet but if I look on them I see two closing brackets on the third expression-part.

- Marcus

Re: Add AGGR function

Tue, 03 May 2016 13:35:47 GMT

Its ok I've sorted it, it works now

Re: Add AGGR function

Tue, 03 May 2016 14:10:37 GMT

Any chance you could help me with this, I'm not sure if I've got the formula correct

sum(if(Companycode = 'JP05' or Companycode = 'KR05' or Companycode = 'ID05' or Companycode = 'TW05',
Sum(Aggr(sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Netrevenue),LOB,BusinessUnit,NeptuneMarket*100*LocalCurrencyRate,
Sum(aggr(sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Netrevenue),LOB,BusinessUnit,NeptuneMarket*LocalCurrencyRate,LOB,BusinessUnit,NeptuneMarket))))))

The expression says it is ok but I get no results

Re: Add AGGR function

marcus_sommer — Tue, 03 May 2016 14:27:17 GMT

You have with:

sum(if(Companycode = 'JP05' or Companycode = 'KR05' or Companycode = 'ID05' or Companycode = 'TW05',
Sum(Aggr(sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Netrevenue),LOB,BusinessUnit,NeptuneMarket*100*LocalCurrencyRate,
Sum(aggr(sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Netrevenue),LOB,BusinessUnit,NeptuneMarket*LocalCurrencyRate,LOB,BusinessUnit,NeptuneMarket))))))

two aggregations nested without using an aggr(). You could of course nest several aggr-functions but it must be look like:

sum(aggr(sum(aggr(sum(aggr(sum(aggr ....

In your case try something like this:

if(match(Companycode, 'JP05', 'KR05', 'ID05', 'TW05'),
Sum(Aggr(sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,

COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Netrevenue), LOB,BusinessUnit,NeptuneMarket)

* 100 * LocalCurrencyRate,
Sum(aggr(sum({$<DHR_FiscalYear=,DHR_FiscalMonth=,

COMMONDATE={">=$(DH_LY_YearStart)<=$(DH_LY_YearEnd)"}>} Netrevenue),LOB,BusinessUnit,NeptuneMarket)

* LocalCurrencyRate))

whereby it's not quite clear for me what are the aggr-dimensions and what should be on which point multiplied and also where are the difference between then- and the else-expression? Only the 100 - then would be one aggr-expression enough and the condition would be only for the 100 applied.

- Marcus