Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

table should show filtered values based on 2nd dimension restriction in qliksense

Hi All,

I have a table where i am trying to populate the values fetching it from database.

have 2 dimensions (say: category and brand). have multiple measures (for eg: avg count of prdoductid)

my requirement is :

complete table should show the values only if the count of productid is greater than or equal to 5 (productid >=5), based on the second dimension (brand).

I have tried limitation option available in qliksense with giving expression in the calculated field. also tried calculated expression in the data handling tab. but not successful.  also tried giving aggr function in the expression with if condition and based on brand as dimension. but nothing is working.

Can anybody help me out on this

Thanks and regards

Suraj Rao

2 Solutions

Accepted Solutions

Re: table should show filtered values based on 2nd dimension restriction in qliksense

Why don't you try just this

Count({<Year = {$(=Max(Year))}, Weeknum = {$(=Max(Weeknum))}, Brand = {"=Count({<Year = {$(=Max(Year))}, Weeknum = {$(=Max(Weeknum))}>} product_id) >= 5"}>} product_id)
Highlighted
Partner
Partner

Re: table should show filtered values based on 2nd dimension restriction in qliksense

 

i got the solution for this.

i concatenated 2 fields and used the concatenated field as my dimension to get the values.which worked fine.

concat is done in script .

classification & ' - ' & product_id as pro_class

used this column in dimension with the same above mentioned expression and it worked 

6 Replies

Re: table should show filtered values based on 2nd dimension restriction in qliksense

May be using set analysis within your expression

{<Brand = {"=Count(productid) >= 5"}>}
Partner
Partner

Re: table should show filtered values based on 2nd dimension restriction in qliksense

i tried this and below expression worked 

=If(Aggr(Count({<Year={$(=Max(Year))},Weeknum={$(=Max(Weeknum))}>}product_id),brand)>=5,
Count({<Year={$(=Max(Year))},Weeknum={$(=Max(Weeknum))}>}product_id),0)

but my pivot is not toatalling the sub totals instead giving me 0's, i have to  use dimensionality and integrate it to the above expression. 

need help please 

Re: table should show filtered values based on 2nd dimension restriction in qliksense

Why don't you try just this

Count({<Year = {$(=Max(Year))}, Weeknum = {$(=Max(Weeknum))}, Brand = {"=Count({<Year = {$(=Max(Year))}, Weeknum = {$(=Max(Weeknum))}>} product_id) >= 5"}>} product_id)
Partner
Partner

Re: table should show filtered values based on 2nd dimension restriction in qliksense

Thanks alot Sunny Talwar.

Worked like a charm. Really helped alot 🙂 

Partner
Partner

Re: table should show filtered values based on 2nd dimension restriction in qliksense

Hi Sunny,

Require a small help on this again. 

We have filtered the product_id >=5 based on brands n this is working fine. I have one more column called classification and want it to be filtered on that column as well

for eg: I have product_id count as 8 for same brands but its for different classification. When i do the pivot in qliksense for one classification its showing proper count of product_id as it has more thn 5 but  same condition does not applies for other classification. because brand name is same, so the count of tht particular classification & brand is showing which is less then 5 .

I have tried few thngs but its not working out. can you help me out here. let me know if you need any further info on this

this is the output i need

classificationbrandCount of productid
ABCTotal10
JSDJS5
DJDJD5
DEFTotal5
TDYDU5
GHITotal23
FDSDDS7
DKNDKO6
UISIOA5
MAJKN5
JKLTotal22
YAYUA5
LJSJJD17
Grand Total 60

 

but im getting this output, blue highlighted in classification JKL & MNO should not display 

classificationbrandCount of productid
ABCTotal10
JSDJS5
DJDJD5
DEFTotal5
TDYDU5
GHITotal23
FDSDDS7
DKNDKO6
YAYUA5
MAJKN5
JKLTotal24
KQDMNF5
LJSJJD17
YAYUA2
MNOTotal1
YAYUA1
Grand Total 63
Highlighted
Partner
Partner

Re: table should show filtered values based on 2nd dimension restriction in qliksense

 

i got the solution for this.

i concatenated 2 fields and used the concatenated field as my dimension to get the values.which worked fine.

concat is done in script .

classification & ' - ' & product_id as pro_class

used this column in dimension with the same above mentioned expression and it worked