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

[su88rao]

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

[sunny_talwar]

May be using set analysis within your expression

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

[su88rao]

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 

[sunny_talwar · Accepted solution]

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)

[su88rao]

Thanks alot Sunny Talwar.

Worked like a charm. Really helped alot 🙂 

[su88rao]

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

classification	brand	Count of productid
ABC	Total	10
JSDJS	5
DJDJD	5
DEF	Total	5
TDYDU	5
GHI	Total	23
FDSDDS	7
DKNDKO	6
UISIOA	5
MAJKN	5
JKL	Total	22
YAYUA	5
LJSJJD	17
Grand Total		60

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

classification	brand	Count of productid
ABC	Total	10
JSDJS	5
DJDJD	5
DEF	Total	5
TDYDU	5
GHI	Total	23
FDSDDS	7
DKNDKO	6
YAYUA	5
MAJKN	5
JKL	Total	24
KQDMNF	5
LJSJJD	17
YAYUA	2
MNO	Total	1
YAYUA	1
Grand Total		63

[su88rao · Accepted solution]

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