Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Aggr in Listbox

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.


1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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).

View solution in original post

22 Replies
Highlighted

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])

Highlighted

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])

Highlighted
Creator II
Creator II

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.

Highlighted

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])

Highlighted
Creator II
Creator II

no sunny its not good at all now,

it is only showing one value from the two.

see the attached

Highlighted

Would you be able to share a sample?

Highlighted
Creator II
Creator II

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?

Highlighted

Highlighted
Creator II
Creator II

thank you sunny,

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

many thanks.