calculated filter - aggr in class

[idogridish2]

Hi All,

I Have A table with three columns the third column is calculated by the first two columns.

the client wont a filter by the third column values, with class() func,

i created a filter but it is not working correctly.

i am attaching a sample.

many thanks.

[sunny_talwar]

When you say it is not working, what exactly is the issue? I tried filtering and it seems to be filtering based on some of the things I selected. Can you elaborate on the issue?

[idogridish2]

Hi Sunny,

1. there shouldn't be this value:

the max value in the normalized score column is 100.

2. when selecting on value from the filter it is selecting some other values as well.

3. i don't wont to hide excluded - this is not so importent.

thanks, is it understood?

[Anonymous]

Hi ido,

I was viewing this, I think the problem is on Site field. Why do you have two Sites with same Site_key?

Without Site it is working well.

Regards!

[sunny_talwar]

In addition to Manuel's comments above, I was able to resolve the 3 (not so important point ) Use this for the list box expression:

=Aggr(Class((Sum({<[Father Supplier Num], [Father Supplier Name], [Supplier Name], [Supplier Num],Site,Year,Qtr>}[Quality Grade])*4)*vQuality_per +

  (Sum({<[Father Supplier Num], [Father Supplier Name], [Supplier Name], [Supplier Num],Site,Year,Qtr>}[OTD Grade])*4)*vOTD_per, 10,'x'),[Father Supplier Name], [Father Supplier Num], [Supplier Name], [Supplier Num],Site,Year,Qtr)

[idogridish2]

Hi Manuel,

there is no Site_Key,

can you tell me how did you understand there is a Site_Key? - there is only site name called site. (mh or ofk)

[Anonymous]

Because with same Site_Key you have 2 or more values. With this model you cannot do what do you want, if you don't use this field your filter will work, but with your model is imposible, because QlikView is returning for same key two distinct values in some records.

Regards!

[sunny_talwar]

I kind agree with manucamon‌ here and I think there is some issue with the site field which is causing you to select both sites based on the selection. I tried creating a unique field, but it seems that even that did not work the way I would have expected. I will continue to troubleshoot.

Best,

Sunny

[Anonymous · Accepted solution]

Hi ido,

See attached.

I have modified your expression and your model, and now it is working.

Expression:

=class(aggr(sum({<[Father Supplier Num], [Father Supplier Name], [Supplier Name], [Supplier Num],Site,Year,Qtr>} Total <[Father Supplier Num],[Supplier Num],Year,Qtr,Source,Reports,Site> ([Quality Grade]*4*vQuality_per)+([OTD Grade]*4*vOTD_per))

    ,[Father Supplier Num],[Supplier Num],Year,Qtr,Source,Reports,Site),10,'x')

The new link table is this:

SITE_KEY_LINK:

LOAD DISTINCT

    SITES_KEY,

    Autonumberhash128(SITES_KEY&'-'&Site) as SITE_KEY_ALT

RESIDENT Sites;

Regards.

[idogridish2]

Manuel thank you very much, i think you forgot to attach!

but i am trying to understand why do we need this other key table?

can you please try to explain?