Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
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