Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (2)
9 Replies
undergrinder
Valued Contributor II

Re: Can I refer a Table total value in a KPI

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

Re: Can I refer a Table total value in a KPI

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.

MVP
MVP

Re: Can I refer a Table total value in a KPI

How about using this in KPI:

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

Not applicable

Re: Can I refer a Table total value in a KPI

Hi Sunny,

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

MVP
MVP

Re: Can I refer a Table total value in a KPI

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

Not applicable

Re: Can I refer a Table total value in a KPI

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

MVP
MVP

Re: Can I refer a Table total value in a KPI

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

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

Not applicable

Re: Can I refer a Table total value in a KPI

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

Re: Can I refer a Table total value in a KPI

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

Community Browser