Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
maelafifi
New Contributor III

Issue with range calculation in Pivot Table

Hi Qlik community,

I am having an issue with a range expression that is calculating a three month average. When I have Customer and Year-Month Dimensions as Rows, I get the number I expect to see. However, the business users don't want the default view in this format. They want the Year-Month and the Measures to be in the column, and the customer alone in the Rows. The problem, then, is that the numbers get all wacky when I move the Year-Month dimension to the column.

Attached below are screenshots of both, and here is the calculation that is calculating the 3-Month rolling DPPM:

((RangeAvg(Above(count({<MonthName, Year, Month>}DISTINCT[iQMS_ID]), 0, 3)) * Avg(1))/

(RangeAvg(Above(Sum({<MonthName, Year, Month>}[QTY_SHIPPED]), 0, 3)) * Avg(1)))

*1000000



NOTE: All other dimensions react perfectly fine when Year-Month is moved from row to column!

Any help with this will be much appreciated!

Mohamed

DPPMNumbersCorrect.png

DPPMNumbersInorrect.png

1 Solution

Accepted Solutions

Re: Issue with range calculation in Pivot Table

Try this

((RangeAvg(Before(count({<MonthName, Year, Month>}DISTINCT[iQMS_ID]), 0, 3)) * Avg(1))/

(RangeAvg(Before(Sum({<MonthName, Year, Month>}[QTY_SHIPPED]), 0, 3)) * Avg(1)))

*1000000

6 Replies

Re: Issue with range calculation in Pivot Table

Try this

((RangeAvg(Before(count({<MonthName, Year, Month>}DISTINCT[iQMS_ID]), 0, 3)) * Avg(1))/

(RangeAvg(Before(Sum({<MonthName, Year, Month>}[QTY_SHIPPED]), 0, 3)) * Avg(1)))

*1000000

maelafifi
New Contributor III

Re: Issue with range calculation in Pivot Table

Thanks Sunny. That worked, but now when I pivot the other way, I get null values. (I prefer this method over the previous, in which incorrect numbers were displayed; the method you've provided is not lying to business users) That said, is there any way to make this dynamic, so that no matter where you pivot, I get the correct numbers?

Regards,

Mohamed

Re: Issue with range calculation in Pivot Table

It is your lucky day... try this

Aggr(

((RangeAvg(Above(count({<MonthName, Year, Month>}DISTINCT[iQMS_ID]), 0, 3)) * Avg(1))/

(RangeAvg(Above(Sum({<MonthName, Year, Month>}[QTY_SHIPPED]), 0, 3)) * Avg(1)))

*1000000

, Customer, ([Year-Month], (NUMERIC)))

maelafifi
New Contributor III

Re: Issue with range calculation in Pivot Table

Hmm, that didn't seem to work. No worries. Your initial reply will work for first release, and I will try working on making it dynamic before next release.

Thanks!

Re: Issue with range calculation in Pivot Table

Oh really? Is Year-Month a date field or text?

maelafifi
New Contributor III

Re: Issue with range calculation in Pivot Table

It WAS a text field, but I made it a date field and now it works.

Much appreciated, Sunny!

Community Browser