# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Nov. 30th, Webinar! Do More With Qlik Cloud Catalog & Lineage: REGISTER
cancel
Showing results for
Did you mean:
Contributor III

## Qliksense columns based on measures

Hi All,

we want to set the pivot table column, can you help to share some ideas? Sample as below,

we want to set B and C for '2020YTD' and D for 'Others', how can i do that? Also attached the qvf here. Thanks.

1 Solution

Accepted Solutions

Here is the front end solution

Data:
A,B,C,D
Frank,23,33,44
Tony,45,66,77,12
Taylor,12,65,34,78
Emily,45,66,88,32
];

In Pivot table Row add column A

In Pivot table Column add below two synthetic dimension

=ValueList('2020YTD','Others')  --> Type

=ValueList('B','C','D')  ---> Measure

=if(ValueList('2020YTD','Others')='2020YTD' and ValueList('B','C','D')='B',
sum(B),
if(ValueList('2020YTD','Others')='2020YTD' and ValueList('B','C','D')='C',
sum(C),
if(ValueList('2020YTD','Others')='Others' and ValueList('B','C','D')='D',
sum(D),0)))

Go to Add-On and uncheck  Include zero values

6 Replies
Creator II

hi @zhaohaifeng ,

here is the solution..

``````test:
CrossTable (attribute,value,1)

A,B,C,D
Frank,23,33,44
Tony,45,66,77,12
Taylor,12,65,34,78
Emily,45,66,88,32
];

NoConcatenate
column:
attribute,
value,
if(attribute='B' or attribute='C' ,'2020YTD' ,
if(attribute='D','Others')) as Type
resident test;

drop table test;``````

Output:

Regards,

raji

Contributor III
Author

Thanks Raji for your help. It works, but do you know any other ways in the sheet, not in script, as if some expressions need calute in the sheet, it will not work. Can you share some ideas? Thanks in advance.

Best Regards,

Haifeng

You can use the below as one of the Dimension.

``````if(attribute='B' or attribute='C' ,'2020YTD' ,
if(attribute='D','Others'))``````
Contributor III
Author

But this attribute still from script, can you share some ideas only from visualization? Thanks

Contributor III
Author

Also shared the sample qvf here, can you help to check and share ideas? Thanks.

Here is the front end solution

Data:
A,B,C,D
Frank,23,33,44
Tony,45,66,77,12
Taylor,12,65,34,78
Emily,45,66,88,32
];

In Pivot table Row add column A

In Pivot table Column add below two synthetic dimension

=ValueList('2020YTD','Others')  --> Type

=ValueList('B','C','D')  ---> Measure

=if(ValueList('2020YTD','Others')='2020YTD' and ValueList('B','C','D')='B',
sum(B),
if(ValueList('2020YTD','Others')='2020YTD' and ValueList('B','C','D')='C',
sum(C),
if(ValueList('2020YTD','Others')='Others' and ValueList('B','C','D')='D',
sum(D),0)))

Go to Add-On and uncheck  Include zero values

Tags
Community Browser