topic Re: calculated filter - aggr in class in QlikView

calculated filter - aggr in class

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

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.

Re: calculated filter - aggr in class

sunny_talwar — Wed, 15 Jun 2016 09:12:12 GMT

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?

Re: calculated filter - aggr in class

idogridish2 — Wed, 15 Jun 2016 09:24:50 GMT

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?

Re: calculated filter - aggr in class

Anonymous — Wed, 15 Jun 2016 10:17:32 GMT

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!

Re: calculated filter - aggr in class

sunny_talwar — Wed, 15 Jun 2016 10:22:30 GMT

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)

Re: calculated filter - aggr in class

idogridish2 — Wed, 15 Jun 2016 10:45:09 GMT

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)

Re: calculated filter - aggr in class

Anonymous — Wed, 15 Jun 2016 10:54:21 GMT

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!

Re: calculated filter - aggr in class

sunny_talwar — Wed, 15 Jun 2016 11:28:03 GMT

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

Re: calculated filter - aggr in class

Anonymous — Wed, 15 Jun 2016 12:16:45 GMT

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.

Re: calculated filter - aggr in class

idogridish2 — Thu, 16 Jun 2016 23:19:00 GMT

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?

Re: calculated filter - aggr in class

Anonymous — Fri, 17 Jun 2016 06:14:26 GMT

Hi Ido,

I don't know why attachment has not uploaded.

I need this new key because I need 1 record by 1 key to do your filter, this is your issue on your document.

Regards!

Re: calculated filter - aggr in class

Anonymous — Fri, 17 Jun 2016 06:34:20 GMT

I have attached my Example.

Regards!

Re: calculated filter - aggr in class

idogridish2 — Fri, 17 Jun 2016 19:41:48 GMT

Thank you very much Manuel,

God bless.