Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
May be using set analysis within your expression
{<Brand = {"=Count(productid) >= 5"}>}
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
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)
Thanks alot Sunny Talwar.
Worked like a charm. Really helped alot 🙂
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 |
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