Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
bshyde22
New Contributor III

AGGR Function Not working for Partial Sum Percentages

Good Morning,

I am developing an app in which the users requirements are to calculate the YoY % Growth for different sales categories by month. Since the user wanted to be able to expand and collapse field dimensions (i.e. Category and Month) I used a Pivot table to display the data. One nuance in the requirements was that the user wanted to be able to see Actuals and Forecast. (i.e. Actual sales would be represented by months that have already completed while current month and future months would be displayed as Forecast sales.) The SUM(AGGR( function works fine with the IF Statement I have in terms of 2018 sales, 2017 sales and YoY Sales Difference. However when I use the AGGR function for the YoY % Growth, the partial sum total is incorrect. In my example below the partial sum reads -40% when it should be +26%.

I need assistance getting correct value shown on the YoY % Growth line.

If anything is this request is confusing I'd be glad to help clarify.

Thanks in advance.

-B-

1 Solution

Accepted Solutions
YoussefBelloum
Esteemed Contributor

Re: AGGR Function Not working for Partial Sum Percentages

Hi,

try this as an expression for YoY %:

=(SUM(AGGR(IF(SUM({<YEAR={'2018'},SCENARIO={'ACTUALS'}>}UNITS)=0,SUM({<YEAR={'2018'},SCENARIO={'FORECAST'}>}UNITS),SUM({<YEAR={'2018'},SCENARIO={'ACTUALS'}>}UNITS)),MONTH, CATEGORY))

-

SUM(AGGR(IF(SUM({<YEAR={'2017'},SCENARIO={'ACTUALS'}>}UNITS)=0,SUM({<YEAR={'2017'},SCENARIO={'FORECAST'}>}UNITS),SUM({<YEAR={'2017'},SCENARIO={'ACTUALS'}>}UNITS)),MONTH, CATEGORY)))

/

SUM(AGGR(IF(SUM({<YEAR={'2017'},SCENARIO={'ACTUALS'}>}UNITS)=0,SUM({<YEAR={'2017'},SCENARIO={'FORECAST'}>}UNITS),SUM({<YEAR={'2017'},SCENARIO={'ACTUALS'}>}UNITS)),MONTH, CATEGORY))

3 Replies
YoussefBelloum
Esteemed Contributor

Re: AGGR Function Not working for Partial Sum Percentages

Hi,

try this as an expression for YoY %:

=(SUM(AGGR(IF(SUM({<YEAR={'2018'},SCENARIO={'ACTUALS'}>}UNITS)=0,SUM({<YEAR={'2018'},SCENARIO={'FORECAST'}>}UNITS),SUM({<YEAR={'2018'},SCENARIO={'ACTUALS'}>}UNITS)),MONTH, CATEGORY))

-

SUM(AGGR(IF(SUM({<YEAR={'2017'},SCENARIO={'ACTUALS'}>}UNITS)=0,SUM({<YEAR={'2017'},SCENARIO={'FORECAST'}>}UNITS),SUM({<YEAR={'2017'},SCENARIO={'ACTUALS'}>}UNITS)),MONTH, CATEGORY)))

/

SUM(AGGR(IF(SUM({<YEAR={'2017'},SCENARIO={'ACTUALS'}>}UNITS)=0,SUM({<YEAR={'2017'},SCENARIO={'FORECAST'}>}UNITS),SUM({<YEAR={'2017'},SCENARIO={'ACTUALS'}>}UNITS)),MONTH, CATEGORY))

bshyde22
New Contributor III

Re: AGGR Function Not working for Partial Sum Percentages

Youssef,

The formula worked perfectly. Thanks for your assistance!

-B-

YoussefBelloum
Esteemed Contributor

Re: AGGR Function Not working for Partial Sum Percentages

You're welcome

Good luck