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: 
Not applicable

Can I refer a Table total value in a KPI

Can I refer a Table total value in a KPItot.PNG

I want to refer the MAPE total value in a KPI

9 Replies
undergrinder
Specialist II
Specialist II

Hi Shivam,

Yes you can, nevertheless not refer, but calculate the same value.

I think in this case the sum(MAPE) is fair enough. Thanks to data modell the value of KPI will follow the selections as well your table. (If it isn't fulfill your requirement some tip : set expressions, aggr() function and so on.

G.

Not applicable
Author

Hi undergrinder,

That is an average total. See the problem I'm facing is I want to calculate mape. In the above figure. I want to the average of all the rows in MAPE column. The fields in the Mape column are calculated it depends on sales and baseline forecast.

I've created a measure which is calculating      (abs(Sale-Forecast)/sale). This is shown in the MAPE column. Now when i change the total to average it gives me the mean of it which is MAPE.

I want to show this mape(calculated value) in a KPI or any other way in which I can calculate MAPE.

sunny_talwar

How about using this in KPI:

Avg(Aggr((abs(Sale-Forecast)/sale), WEEK))

Not applicable
Author

Hi Sunny,

Nope this isn't working. This isn't giving any output.

sunny_talwar

Is WEEK the only dimension in your chart or are there more dimensions?

Not applicable
Author

Yes Sunny there are more dimension than Week. It has all the dimensions of the calendar hierarchy

sunny_talwar

Try adding all those dimensions to the Aggr() function here:

Avg(Aggr((abs(Sale-Forecast)/sale), WEEK, DATE... etc))

Not applicable
Author

I want it to be dynamic. It should change according to the dimension I'Il select. See I'll explain it to you completely what I really need.

a) I need to calculate MAPE and for that I am using the formula:

"fabs((Sum(Sales) - Sum(Base_Forecast)))/Sum(Sales)".

This is giving me correct Absolulte%Error which is shown in the first figure under mape column. If I change the totals column to average I get MAPE. Which is what I want but when I use this formula in a KPI it's applying this formula on the Total's of  Sales & Forecast instead of applying on each row then take an average.

That's why I wanted to know if it's possible to refer table total value.

Any help would be greatly appreciated.

Not applicable
Author

Hey Sunny, Can you please assist me in this problem too https://community.qlik.com/message/1047132#1047132