Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Contributor III

Average and Set Analysis Expression

Hi Community.

I have an issue with incorporating the Average calculation within set analysis.

I have a Dimension called # of Days After Invoice and I need to calculate the average days > 14 days. In the below expression, I am counting the number of sales I have that meet the requirement:

=Count({$<[#of Days After Invoice]={'>14'},Sales,[Date Type]={'VPP Dates'},[Reference_Only]=-{'Yes'}>}[Sales])

I want to create a KPI in a text box that shows the average number of days and I cannot for the life of me figure out how to show this.

Any help rendered is truly appreciated. 

Labels (3)
3 Replies

Re: Average and Set Analysis Expression

Not really clear what you have, but may be try this

=Avg({$<[#of Days After Invoice] = {'>14'},Sales,[Date Type] = {'VPP Dates'}, [Reference_Only]=-{'Yes'}>} [#of Days After Invoice])
Contributor III

Re: Average and Set Analysis Expression

May be like this,

=Count({$<[#of Days After Invoice]={">14"},[Date Type]={'VPP Dates'},[Reference_Only]-={'Yes'}>}[Sales])

Contributor III

Re: Average and Set Analysis Expression

That did not work Sunny, somehow I am getting a larger number than I would in Excel when I export the data file. Do I have to aggregate it (aggr)?