Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

expression for rolling six months

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 YearCurrent Month %
01-201984.81%
02-201983.94%
03-201984.43%
04-201984.51%
05-201986.25%
06-201985.86%
07-201985.19%
08-201985.07%
09-201986.69%
10-201985.50%
11-201987.09%
1 Solution

Accepted Solutions
Highlighted

Re: expression for rolling six months

Try this

RangeAvg(Above(

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

, 0, 6))

View solution in original post

6 Replies
Highlighted

Re: expression for rolling six months

May be this for Previous Six Month Average

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

Re: expression for rolling six months

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?

 

 

Highlighted

Re: expression for rolling six months

What is the expression behind Current Month %? 

Highlighted
Contributor III
Contributor III

Re: expression for rolling six months

this is the expression sunny

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

Highlighted

Re: expression for rolling six months

Try this

RangeAvg(Above(

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

, 0, 6))

View solution in original post

Highlighted
Contributor III
Contributor III

Re: expression for rolling six months

wow. it worked. thank you so much Sunny