topic Re: Aggr in Listbox in QlikView

Aggr in Listbox

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

Hi All,

I have a pivot table that display trands,

with two calculated dimensions "Overall Trend" and "Last Two Qtr Trend"

I am using bundle info to show pics insted of values,

now the client wants this pics as filters that hew can use on the side,

"Overall Trend" calculated dimension in the table:

=aggr(if(sum(AttentionFlag)>=2,Only({$<Alabet={'a'}>}info(Alabet)),Only({$<Alabet={'b'}>}info(Alabet))),[Father Supplier Name])

"Overall Trend" calculated dimension in the listbox:

=aggr(if(sum(AttentionFlag)>=2,Only({$<[Father Supplier Name]=,Alabet={'a'}>}info(Alabet)),Only({$<[Father Supplier Name]=,Alabet={'b'}>}info(Alabet))),[Father Supplier Name])

the calculation in the listbox keep excluding the other picture,

See th Attached.

help is appreciated.

thanks.

Re: Aggr in Listbox

sunny_talwar — Wed, 08 Jun 2016 18:44:06 GMT

Try this:

=Aggr(If(Sum({<[Father Supplier Name] = >} AttentionFlag) >= 2, Only({$<[Father Supplier Name] = ,Alabet = {'a'}>} info(Alabet)), Only({$<[Father Supplier Name] =, Alabet = {'b'}>} info(Alabet))), [Father Supplier Name])

Re: Aggr in Listbox

sunny_talwar — Wed, 08 Jun 2016 18:47:49 GMT

Or may be this:

=Aggr(If(Sum({<[Father Supplier Name] = , Alabet =>} AttentionFlag) >= 2, Only({$<[Father Supplier Name] = ,Alabet = {'a'}>} info(Alabet)), Only({$<[Father Supplier Name] =, Alabet = {'b'}>} info(Alabet))), [Father Supplier Name])

Re: Aggr in Listbox

idogridish2 — Wed, 08 Jun 2016 19:07:32 GMT

thank you sunny it seems ok but i have another filer,

"Last Two Qtr Trend" that i am allso ignoring [Father Supplier Name],

"Last Two Qtr Trend" in this listbox:

=aggr(only({$<AttentionSign-={'a'},[Father Supplier Name]=,[Year-Qtr-Num]={'$(=max([Year-Qtr-Num]))'}>}info(AttentionSign)),[Father Supplier Name])

and its ok,

but when i am filtering one of the filters the table is filtering ok but in the same scenario i am filtering

the other filter and it is canceling the previus filter,

i believe it is because we are ignoring [Father Supplier Name] the second filter is catching all the [Father Supplier Name]

in the model without paying attention to the preveius filter.

is ther somthing i can do?

hope it's understood.

thanks.

Re: Aggr in Listbox

sunny_talwar — Wed, 08 Jun 2016 19:19:53 GMT

Never tried this before, can you give this a try:

=Aggr(Alt(Only({$<[Father Supplier Name] = {"=Sum({<[Father Supplier Name] = >} AttentionFlag) >= 2"}, Alabet = {'a'}>} info(Alabet)), Only({$<[Father Supplier Name] = {"=Sum({<[Father Supplier Name] = >} AttentionFlag) < 2"}, Alabet = {'b'}>} info(Alabet))), [Father Supplier Name])

Re: Aggr in Listbox

idogridish2 — Wed, 08 Jun 2016 19:34:37 GMT

no sunny its not good at all now,

it is only showing one value from the two.

see the attached

Re: Aggr in Listbox

sunny_talwar — Wed, 08 Jun 2016 19:36:32 GMT

Would you be able to share a sample?

Re: Aggr in Listbox

idogridish2 — Wed, 08 Jun 2016 19:43:30 GMT

I am not sure, i need to check - i dont think the client will approve.

is it simple to scrable the data can you guide me how?

Re: Aggr in Listbox

sunny_talwar — Wed, 08 Jun 2016 19:45:11 GMT

Look here to scramble the data

Preparing examples for Upload - Reduction and Data Scrambling

Re: Aggr in Listbox

idogridish2 — Wed, 08 Jun 2016 19:47:56 GMT

thank you sunny,

we will continue this later after i will scramble the data.

many thanks.

Re: Aggr in Listbox

idogridish2 — Wed, 08 Jun 2016 20:07:54 GMT

Sample Attached.

Re: Aggr in Listbox

sunny_talwar — Wed, 08 Jun 2016 20:32:35 GMT

I think its time we get the experts involved here. May be swuehl‌ can offer you his expert advice

Re: Aggr in Listbox

swuehl — Wed, 08 Jun 2016 22:23:01 GMT

Maybe like attached?

Re: Aggr in Listbox

sunny_talwar — Wed, 08 Jun 2016 22:24:34 GMT

Stefan I am on a mobile device and I can't wait to see what the solution would be. Do you mind posting the expression/s?

Re: Aggr in Listbox

swuehl — Wed, 08 Jun 2016 22:48:24 GMT

I am basically using an alternate state AS1 for the second list box and then use

=aggr(only({<AttentionSign-={'a'},[Father Supplier Name]=,[Year-Qtr-Num]={'$(=max({$}[Year-Qtr-Num]))'}>}info(AttentionSign)),[Father Supplier Name])

and as expression in the chart

=AVG({$*AS1}[Final Score])

there currently might be some minor open issue with regard to e.g. selections in time (not sure how the requirements are here, the current list box 1 also seems to not show the correct filter options when e.g. selecting a quarter).

Re: Aggr in Listbox

sunny_talwar — Wed, 08 Jun 2016 22:51:55 GMT

No way to create this list box without using the if statement? I think the issue is related to the if statement getting triggered on selection and might be avoided if we can avoid the if statement.

Re: Aggr in Listbox

idogridish2 — Thu, 09 Jun 2016 18:01:32 GMT

thank you very much guys very very impressive.

now i have one more issues:

the client also want the table to be sorted i managed to sort the "Overall Trend" but need to sort "Last Two Qtr Trend"

(the arrows).

again thank you very very much !

Re: Aggr in Listbox

sunny_talwar — Thu, 09 Jun 2016 20:23:54 GMT

May be sort using this:

RangeSum(If(Sum(AttentionFlag) = 0, 100000, Sum(AttentionFlag)*100000), ORD(Only({$<[Year-Qtr-Num]={'$(=max([Year-Qtr-Num]))'}>} AttentionSign)))

Re: Aggr in Listbox

sunny_talwar — Thu, 09 Jun 2016 20:27:03 GMT

Or this:

RangeSum(If(Sum(AttentionFlag) = 0 or Sum(AttentionFlag) = 1, 100000, 200000), ORD(Only({$<[Year-Qtr-Num]={'$(=max([Year-Qtr-Num]))'}>} AttentionSign)))

Re: Aggr in Listbox

idogridish2 — Thu, 09 Jun 2016 20:57:57 GMT

Thanks Sunny, it seems to work in the sample but not in the model itself (it is better then what it was)

can you take another look at the new attached - i didn't realize the model was so small the first time.

so here is the not reduced model.

Many thanks.