Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

matthew_morge
New Contributor III

Pivot Table Total of a FABS(Sum...

Hi Guys,

I have been working on a forecast accuracy report in Qlik Sense. I have a pivot table displaying daily sales qty's & forecast qty's, volume error and percentage error measures. The issue that I am having is that when applying the total function to the pivot table, the error volume measure is not picking up the values from the FAB calc. It is netting the negatives off of the positive values and giving a much lower result that expected.

The expression I have is as follows:

=FABS(SUM({$<[CATALOG_NO],[TIMEFENCE_FLAG]={Y},[VALID_SET_FLAG]={Y}>}[FORECAST_LEV0])-

SUM({$<[CATALOG_NO],[TIMEFENCE_FLAG]={Y},[VALID_SET_FLAG]={Y}>}DESIRED_QTY))

What I have read indicates that I need to incorporate the AGGR function but being new to QS I am struggling on how to do this correctly.

Help would be gratefully received.

Thanks,

Matt

1 Solution

Accepted Solutions
MVP
MVP

Re: Pivot Table Total of a FABS(Sum...

Try this

Sum(Aggr(

fabs(Sum({$<[CATALOG_NO], [TIMEFENCE_FLAG] = {Y}, [VALID_SET_FLAG] = {Y}>} [FORECAST_LEV0]) -

Sum({$<[CATALOG_NO], [TIMEFENCE_FLAG] = {Y}, [VALID_SET_FLAG] = {Y}>} DESIRED_QTY))

, YearWeek, WeekDay))

5 Replies
Digvijay_Singh
Honored Contributor III

Re: Pivot Table Total of a FABS(Sum...

Are you saying you are getting negative result of your expression , even after using FABS?

Also is this working for you - [TIMEFENCE_FLAG]={Y}, not sure if you need it like this - {'Y'}, single quote around Y.

If possible share your dimensions, sample data, app for better responses.

matthew_morge
New Contributor III

Re: Pivot Table Total of a FABS(Sum...

Hi Digvijay,

The results I am getting are all positive and can be seen in the screenshot below. I think you can clearly see that both totals on both rows are giving the net of Positives + Negatives = 96 where the intended result would be 742, basically the total variance from forecast.

ForecastAccuracyExample.PNG

The Dimension to be totaled would be [WeekDay].

If you could help, steer me in the right direction I would be extremely grateful.

Thanks,

Matt

dvickers
New Contributor

Re: Pivot Table Total of a FABS(Sum...

Could you try an Aggr() function?

Aggr() accepts two arguments: 1) aggregate expression, and 2) the dimension to aggregate on e.g year/month, weekday, customer.

You could also pass resulta from Aggr() into an aggregate function e.g. SUM(), MAX(), etc.

-Davis

MVP
MVP

Re: Pivot Table Total of a FABS(Sum...

Try this

Sum(Aggr(

fabs(Sum({$<[CATALOG_NO], [TIMEFENCE_FLAG] = {Y}, [VALID_SET_FLAG] = {Y}>} [FORECAST_LEV0]) -

Sum({$<[CATALOG_NO], [TIMEFENCE_FLAG] = {Y}, [VALID_SET_FLAG] = {Y}>} DESIRED_QTY))

, YearWeek, WeekDay))

matthew_morge
New Contributor III

Re: Pivot Table Total of a FABS(Sum...

Thanks Sunny, thats great, very logical when you know how to structure the expressions, in fact I had a related issue as well which I have just resolved based on this.

Kind regards,

Matt

Community Browser