topic Re: expression for rolling six months in QlikView

expression for rolling six months

prrajendran — Sat, 16 Nov 2024 19:39:16 GMT

Hi Experts,

i have the following data with Month names and the corresponding %. i have to build a chart/table with Month name as dimension and two expressions 1. Current Month % as it is 2. Previous six months average.

For example, if the Month name is September-2019, then current month % is 86.69% and Previous six months average should be 85.03%. can you please help me with the expression? i cannot do this in the data.

Input:

Month Year	Current Month %
01-2019	84.81%
02-2019	83.94%
03-2019	84.43%
04-2019	84.51%
05-2019	86.25%
06-2019	85.86%
07-2019	85.19%
08-2019	85.07%
09-2019	86.69%
10-2019	85.50%
11-2019	87.09%

Re: expression for rolling six months

sunny_talwar — Mon, 18 Nov 2019 11:39:56 GMT

May be this for Previous Six Month Average

RangeAvg(Above([Current Month %], 0, 6))

Re: expression for rolling six months

prrajendran — Mon, 18 Nov 2019 11:43:58 GMT

sunny,

thank you for replying. but my Current month % is a calculated field not direct from data. can you please help me with set expression?

Re: expression for rolling six months

sunny_talwar — Mon, 18 Nov 2019 11:44:56 GMT

What is the expression behind Current Month %?

Re: expression for rolling six months

prrajendran — Mon, 18 Nov 2019 11:50:09 GMT

this is the expression sunny

count (DISTINCT{<[ProductNum]={'>0'}>}Sales)
/
(count (DISTINCT{<[ProductNum]={'>0'}>}Sales) + count (DISTINCT{<[InvoiceNum]={'>0'}>}Sales))

Re: expression for rolling six months

sunny_talwar — Mon, 18 Nov 2019 11:51:47 GMT

Try this

RangeAvg(Above( count (DISTINCT{<[ProductNum]={'>0'}>}Sales) / (count (DISTINCT{<[ProductNum]={'>0'}>}Sales) + count (DISTINCT{<[InvoiceNum]={'>0'}>}Sales)) , 0, 6))

Re: expression for rolling six months

prrajendran — Mon, 18 Nov 2019 12:03:24 GMT

wow. it worked. thank you so much Sunny