Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
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!