Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

beck110979
Valued Contributor III

pivot table like this

Hi Folks,

i got question, i have a pivot table (please see attached example)

above table is my current table, the below table is my expected output,

i have 3 Dimension: year, project, cost center and as expression i am using: count(contract)

how can i exlude values within pivot table: i mean, i want just show the expression: count (contract) to dimension: project

(please see attached example and below table)

thanks a lot

beck

8 Replies
laddu_927
Valued Contributor

Re: pivot table like this

Try using if(Dimensionality()=2,{your expression},'  ')

beck110979
Valued Contributor III

Re: pivot table like this

Hi Shiva, it does look great, but what if i replace the order of my dimension whithin my table,.

i mean: i move the dimension: project in the first order, and dimension: cost center in the second order,

can i fixit this view?

please let me know, if i explained not well

thanks a lot

beck

laddu_927
Valued Contributor

Re: pivot table like this

Hi beck,

Am not completely sure, but I think we can calculate the columno() of the dimension (project in this case) and then use that number to fix the dimensionality at which we want the count.

beck110979
Valued Contributor III

Re: pivot table like this

Shiva,

can you show me your way of proceeding or example?

laddu_927
Valued Contributor

Re: pivot table like this

Apologies beck, Looks like columnno() is for a different purpose. I am unable to give a working solution for you.

Let's see if any expert will help us out.

beck110979
Valued Contributor III

Re: pivot table like this

No Problem Shiva, nevertheless thanks a lot for your time and help

arvind654
Honored Contributor II

Re: pivot table like this

We cannot hide the column totally in Qlik Sense.

the only solution I can think of is using Section Access. Even that will not hide the column but instead will show - as values for restricted field.

But that's not a good idea to use SA just to hide a field when actually not implementing security.

OR

Use

let hideVar=1;  in script

and do a preceding load

Load *,

if($(hideVar)<>1,CostCenter) as NewCostCenter;

Load

....

From...

The result will vary and the totals will not match if you hide it this way. I'm not sure why you wanna hide the column, why don't just delete that dimension, use Year as a Filter

beck110979
Valued Contributor III

Re: pivot table like this

Hi Aehman,

first of all: thanks a lot for your feedback, i am using already a concept of Access Section, i should think about another approach to solve this issue

But Thanks a lot