Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ndeeleysww
Contributor

Filter one side of pivot table

Hi all,

Think this is not achievable, but I need to filter just one side of a pivot table.

I have a count of equipment by work group. This count never changes as its the total of the equipment. Joined to that is a count of work orders on that equipment, filerable by cost centre. I can create the table, but when I add in the filter it of course filters the whole table, and reduces the equipment count accordingly.

Any ideas how I can over come this one?

Thanks all

Neil

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Filter one side of pivot table

Replace $ with 1.

=Count({1<[Equipment Type]-={" "}>} [Equipment Type])


Note: Now your expression won't change to any selections you make. If that is not what you want, there may be other ways to achieve what you are looking for. You just need to clarify your requirement.


HTH


Best,

Sunny

11 Replies
MVP
MVP

Re: Filter one side of pivot table

If I understand you question properly, I think you can use this {1} within your expression for total of the equipment for it to be not filtered by any selection.

I hope this will help.

Best,

Sunny

ndeeleysww
Contributor

Re: Filter one side of pivot table

Brilliant - thank you. Do you know where I use the bit in the expression?

count({$ <[Equipment Type]-={" "}>} [Equipment Type])

MVP
MVP

Re: Filter one side of pivot table

Replace $ with 1.

=Count({1<[Equipment Type]-={" "}>} [Equipment Type])


Note: Now your expression won't change to any selections you make. If that is not what you want, there may be other ways to achieve what you are looking for. You just need to clarify your requirement.


HTH


Best,

Sunny

ndeeleysww
Contributor

Re: Filter one side of pivot table

Fantastic! Thanks for your help Sunny.

MVP
MVP

Re: Filter one side of pivot table

No Problem Just curious to know if that did exactly what you were looking to do?

ndeeleysww
Contributor

Re: Filter one side of pivot table

Yes - the equipment count remains the same, but the equipment in relation to the filter changes. It does reorder the columns in the pivot table tho, which is a pain.

MVP
MVP

Re: Filter one side of pivot table

Reorder the columns? Really? I don't think it should do that. Can you show a screenshot of with and without selection and may be I can help you fix that as well.

Best,

Sunny

ndeeleysww
Contributor

Re: Filter one side of pivot table

It's because I have a sort Match on the columns to put them in the required order:

Match([Safety and Legislative],

'MCERT',

'Fixed Gas Monitors',

'Crypto',

'Chlorination and Sulphination',

'Inlet Protection',

'Fixed Generators',

'Mobile Generators'

).

pic1.png

As soon as I choose a filter the match is removed:

pic2.png

Thanks for all your help Sunny!

MVP
MVP

Re: Filter one side of pivot table

Try using this as your sort expression:

Match(Only({1}[Safety and Legislative]),

'MCERT',

'Fixed Gas Monitors',

'Crypto',

'Chlorination and Sulphination',

'Inlet Protection',

'Fixed Generators',

'Mobile Generators'

).