Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Add AGGR function

Hi

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Add AGGR function

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

9 Replies

Re: Add AGGR function

Maybe try

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

MVP
MVP

Re: Add AGGR function

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).

MVP & Luminary
MVP & Luminary

Re: Add AGGR function

You could try this:

sum(aggr(

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))

, Region))

- Marcus

Not applicable

Re: Add AGGR function

Hi

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

Re: Add AGGR function

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

MVP & Luminary
MVP & Luminary

Re: Add AGGR function

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

Not applicable

Re: Add AGGR function

Its ok I've sorted it, it works now

Not applicable

Re: Add AGGR function

Hi

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

MVP & Luminary
MVP & Luminary

Re: Add AGGR function

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