Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
matthew_morge
Contributor III
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
sunny_talwar

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

View solution in original post

5 Replies
Digvijay_Singh

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III

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

sunny_talwar

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
Contributor III
Contributor III
Author

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