Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
diane_yu
Contributor II
Contributor II

Help on Set Analysis for fixed value in reference line

Hello,

I'm working on reference line but the current formula is not working correctly.   If the filter is not applied, the current formula is working correctly but when filter is applied, the average is based on what's selected.  

I have a data set that has duplicated UID and I need to calculate the average based on unique UID the must meet below criteria:

1) Exclude any zero from DIRRECTTIME_TOTAL. 

2) UID must have ":"

=num(AVG(AGGR(AVG(distinct { 1 < UID={"*:*"}, DIRECTTIME_TOTAL-={"0"} >} DIRECTTIME_TOTAL),UID)),'#,###,##')

Thanks for any help!

1 Solution

Accepted Solutions
sunny_talwar

May be this

=Num(Avg({1<UID={"*:*"}, DIRECTTIME_TOTAL-={"0"}>} Aggr(Avg(DISTINCT {1<UID={"*:*"}, DIRECTTIME_TOTAL-={"0"}>} DIRECTTIME_TOTAL), UID)),'#,###,##')

View solution in original post

6 Replies
vishsaggi
Champion III
Champion III

Try this?

= num(AVG({$< UID = {"*:*"}, DIRECTTIME_TOTAL -= {'0'} >} AGGR(AVG(distinct {$< UID = {"*:*"}, DIRECTTIME_TOTAL -= {'0'} >} DIRECTTIME_TOTAL),UID)),'#,###,##')

Else share some sample data with expected output?

sunny_talwar

May be this

=Num(Avg({1<UID={"*:*"}, DIRECTTIME_TOTAL-={"0"}>} Aggr(Avg(DISTINCT {1<UID={"*:*"}, DIRECTTIME_TOTAL-={"0"}>} DIRECTTIME_TOTAL), UID)),'#,###,##')

diane_yu
Contributor II
Contributor II
Author

Thank you for your response but it did not work.

diane_yu
Contributor II
Contributor II
Author

Awesome!  It works like a charm but can you explain to me your process please?

Thanks bunch for your help.

sunny_talwar

Although your inner aggregation was restricted via set analysis, your outer aggregation wasn't. So, when you were making selections, the inner aggregation ignored them just the way you wanted, but outer one still honored them showing you only those results which were in the selection.

In general, it is always a good idea to use the inner set analysis within your outer aggregation set analysis (unless you have a requirement where the row level should not filter based on selections, but your outer one does).

Does this make sense?

diane_yu
Contributor II
Contributor II
Author

Thanks Sunny,

After reading your answer, I looked up about inner and outer aggregation and I found a good article. 

https://community.qlik.com/blogs/qlikviewdesignblog/2015/10/05/pitfalls-of-the-aggr-function

Thank you Sunny again for your explanations.