topic Re: Help on Set Analysis for fixed value in reference line in QlikView

Help on Set Analysis for fixed value in reference line

diane_yu — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Help on Set Analysis for fixed value in reference line

vishsaggi — Mon, 12 Jun 2017 15:21:27 GMT

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?

Re: Help on Set Analysis for fixed value in reference line

sunny_talwar — Mon, 12 Jun 2017 15:45:24 GMT

May be this

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

Re: Help on Set Analysis for fixed value in reference line

diane_yu — Mon, 12 Jun 2017 16:06:23 GMT

Thank you for your response but it did not work.

Re: Help on Set Analysis for fixed value in reference line

diane_yu — Mon, 12 Jun 2017 16:08:54 GMT

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

Thanks bunch for your help.

Re: Help on Set Analysis for fixed value in reference line

sunny_talwar — Mon, 12 Jun 2017 16:16:34 GMT

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?

Re: Help on Set Analysis for fixed value in reference line

diane_yu — Mon, 12 Jun 2017 16:38:28 GMT

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.