
Re: How do I calculate MAPE in Qlik Sense?
Shivam Sarin Jun 9, 2016 6:25 AM (in response to Shivam Sarin)
Re: How do I calculate MAPE in Qlik Sense?
Michiel van Lutterveld Jun 9, 2016 8:01 AM (in response to Shivam Sarin)
Re: How do I calculate MAPE in Qlik Sense?
Shivam Sarin Jun 9, 2016 9:39 AM (in response to Michiel van Lutterveld )Hi Michiel,
I tried this way but it's not giving me the correct answer, in fact, it's giving me incorrect value for Difference b/w the forecast as well
I tried using the formula
fabs((Sum(Init_Forecast)  Sum(Base_Forecast)))/Sum(Init_Forecast) which is giving me correct for each row but the only problem I'm facing is it's summing up an entire column of Initial and baseline forecast then applying the formula. What I want is it should apply the formula on each row as it is shown in the MAPE column then take the average of it. I've got the correct MAPE in the table but I don't know how to display it in a KPI.

Re: How do I calculate MAPE in Qlik Sense?
Michiel van Lutterveld Jun 9, 2016 10:22 AM (in response to Shivam Sarin)Hi Shivan,
I'm sorry but I don't think I understand your question. In attached app I've added a month dimension to your Excel data (the data in your first message). No matter what kind of object I'm using the MAPE keeps rolling up to 74%. Just like you highlighted in your first message.
If the enclosed app doesn't help, could you please share yours or show the expressions you use?
Cheers,
Michiel

MAPE.qvf 176.0 K

Re: How do I calculate MAPE in Qlik Sense?
Shivam Sarin Jun 10, 2016 6:45 AM (in response to Michiel van Lutterveld )Hi Michiel,
I'll explain you in full.I've made changes in your qvf. Please see that. The 74% you're getting is maybe because of the totals column may be set to auto.

MAPE_new.qvf 176.0 K

Re: How do I calculate MAPE in Qlik Sense?
Shivam Sarin Jun 10, 2016 8:13 AM (in response to Shivam Sarin)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 above figure under ape 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 Initial & Baseline instead of applying on each row then take an average. Is there any way I can refer that table value or how can I get that calculated value in the KPI.
Any help would be greatly appreciated and please let me know If what I'm doing is wrong.
Thanks

Re: How do I calculate MAPE in Qlik Sense?
Sunny Talwar Jun 10, 2016 10:47 AM (in response to Shivam Sarin)
MAPE_new.qvf 176.0 K

Re: How do I calculate MAPE in Qlik Sense?
Shivam Sarin Jun 13, 2016 2:24 AM (in response to Sunny Talwar )Hi Sunny, This works absolutely fine. This will help me for now, but this is static now according to month how can I make this dynamic if want to change the dimension to maybe year or quarter?

Re: How do I calculate MAPE in Qlik Sense?
Sunny Talwar Jun 13, 2016 6:33 AM (in response to Shivam Sarin)This has to be based on a selection, right? Where are you planning to make the selection in Year, Month or Quarter Field?

Re: How do I calculate MAPE in Qlik Sense?
Shivam Sarin Jun 13, 2016 6:47 AM (in response to Sunny Talwar )It could be anything it depends on the customer how he wants to view it. I get that we ca change the dimension to quarter,year or month to get it but can we create an if logic which according to selection would change my dimension.

Re: How do I calculate MAPE in Qlik Sense?
Sunny Talwar Jun 13, 2016 10:29 AM (in response to Shivam Sarin)Not sure how you plan to do this, but something like this should work for you:
Avg(Aggr(fabs((Sum(Sales)  Sum(Base_Forecast)))/Sum(Sales), $(=If(Condition = Something, Month, Year))))
Basically the idea is to provide the condition within the dollar sign expansion here > $(=.....)

Re: How do I calculate MAPE in Qlik Sense?
Shivam Sarin Jun 14, 2016 3:06 AM (in response to Sunny Talwar )Thanks Sunny,
I was able to do it in another way. I preloaded the variance as a calculated colum at the time of data load. I loaded that as a dimension and took average of it. The thing which confused me was when I load this calculated column as a measure it gives a different value as when I load it as a dimension.
Please see the attached screens and please explain me what's wrong here.
1) Calculated column
2) Without the calculated column.
3) After adding the calculated column as dimension
4) After adding calculated column as measure. The values change again to original one

Re: How do I calculate MAPE in Qlik Sense?
Shivam Sarin Jun 16, 2016 2:23 AM (in response to Shivam Sarin)stalwar1 hey did you get a chance to review this ?

Re: How do I calculate MAPE in Qlik Sense?
Sunny Talwar Jun 16, 2016 5:23 AM (in response to Shivam Sarin)I thought this was resolved already since you did it in the script?

Re: How do I calculate MAPE in Qlik Sense?
Shivam Sarin Jun 16, 2016 5:47 AM (in response to Sunny Talwar )Yes currently this is resolved but I'm unable to understand the above behavior in qliksense. Can you please explain me what I am doing wrong?













