Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
zhaohaifeng
Contributor III
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,

zhaohaifeng_1-1594273118677.png

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
Kushal_Chawda

Here is the front end solution

Data:
Load * Inline [
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

Add below 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

 

View solution in original post

6 Replies
raji6763
Creator II
Creator II

hi @zhaohaifeng ,

i hope this is helpful to you.please find attached QVF file.

here is the solution..

 

 

test:
CrossTable (attribute,value,1)
Load * Inline [

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:
load A ,
attribute,
value,
if(attribute='B' or attribute='C' ,'2020YTD' ,
if(attribute='D','Others')) as Type
resident test;

drop table test;

 

 

 

Output:

 
 

column measure.JPG

 

 

 

Regards,

raji

zhaohaifeng
Contributor III
Contributor III
Author

Hi @raji6763 

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

Saravanan_Desingh

You can use the below as one of the Dimension.

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

Hi @Saravanan_Desingh ,

 

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

zhaohaifeng
Contributor III
Contributor III
Author

hi @Saravanan_Desingh , @raji6763 

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

Kushal_Chawda

Here is the front end solution

Data:
Load * Inline [
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

Add below 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