Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik GA: Multivariate Time Series in Qlik Predict: Get Details
cancel
Showing results for 
Search instead for 
Did you mean: 
Sam07
Contributor II
Contributor II

Need to calculate the Median of the results of the Median Over Month done previously

Hello everyone,

I need to calculate the Median of the results generated by the calculation I make of the Median across the months.

Sam07_1-1733743205413.png

 

As you can see in the image of the table I share. In the second column (Median of Each Month), I am calculating the Median of each month to be able to check if the calculation made in the third column is correct.

In the third column (Median Over Month), I make a cumulative sum of the Medians across the fiscal months, for instance, when in May the formula sums the result of the Median of April with that of May and so on.

In the fourth column (Final Median), I am trying to calculate the Median of all the values ​​generated across the months, as in the third column, but as you can see, from July onwards, an error occurs that I cannot identify. Instead of the result for July being 366 as in the third column, it is showing 472. Because of this, the total that should be 388 is being presented as 392.

Could you guys please help to get the right result for the fourth column? One thing, on the third column, it shouldn't being presenting values on December and onward since there's no value for this month and the followings. 

The formula in the second column (Median of Each Month):

Median({<[Fiscal Date]={">=$(=YearStart(max([Fiscal Date])))"},VRMBDAFLG_LOT_APPROVED={'1'},VRMBDAFLG_OUTLIER={'1'}>}VRMBDADAYS_TO_BE_RELEASED)

The formula in the third column (Median Over Month):

Rangesum(
Above(
Median({<[Fiscal Date]={">=$(=YearStart(max([Fiscal Date])))"},VRMBDAFLG_LOT_APPROVED={'1'},VRMBDAFLG_OUTLIER={'1'}>}VRMBDADAYS_TO_BE_RELEASED),0, RowNo(TOTAL)
)
)

The formula in the fourth column (Final Median):

MEDIAN(Aggr(Rangesum(
Above(
Median({<[Fiscal Date]={">=$(=YearStart(max([Fiscal Date])))"},VRMBDAFLG_LOT_APPROVED={'1'},VRMBDAFLG_OUTLIER={'1'}>}VRMBDADAYS_TO_BE_RELEASED),0, RowNo()
)
) , FISCAL_YEAR, FISCAL_MONTH))

Labels (2)
0 Replies