Qlik Community

Ask a Question

Qlik Sense App Development

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

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
caseyjohnson
Contributor III
Contributor III

Locking a Set Analysis Result in a table like Excel $

I have a simple table and I'm trying to figure out how to lock in a calculation using set analysis (or something else?) for the calculation of other fields. So far, I'm striking out. What I'm trying to do is use a certain field like you would in Excel (ex: $C$15) to make some calculations throughout the table.

I'd like to be able to use the 2018 Avg Sales to calculate the percent changes throughout the table without hard setting a value in the calculation. Would need it to be able to accept Dimension changes as well.

Table:

Year      Sum(Sales)     Avg(Sales)    Percent Change (in Avg)

2018     20000              500               0%

2017     17500              425               ?   

2016     15000              413               ?

2015     13000              435               ?

This was my attempt:

Avg(Sales) -  Avg($< Year = {2018}>} Sales) / Avg($< Year = {2018}>} Sales)


So for 2017, it should show a (-15%) change and so on.

1 Solution

Accepted Solutions
sunny_talwar

There are two ways you can do this... if you always want to divide by 2018's avg sale... try this

Avg(Sales)/Avg(TOTAL {$<Year = {2018}>} Sales) - 1

If you want to divide by the avg sales of next year, then try this

Avg(Sales)/Above(Avg({<Year>}Sales)) - 1

View solution in original post

2 Replies
sunny_talwar

There are two ways you can do this... if you always want to divide by 2018's avg sale... try this

Avg(Sales)/Avg(TOTAL {$<Year = {2018}>} Sales) - 1

If you want to divide by the avg sales of next year, then try this

Avg(Sales)/Above(Avg({<Year>}Sales)) - 1

View solution in original post

caseyjohnson
Contributor III
Contributor III

That's it! Thank you Sunny!!