Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Qliksense pivot table Create column based on measures

Hi all,

we want to create column  based on measures, for sample as below, could you help to share some ideas? Thanks in advance.

For below screenshot what we can achive in Qliksense now, we want to create column, for column C(Budget) and D(Consumption) we create column'A' for them, for E(Scan),F(Second_scan) and G(Retailer_Scan), we create column 'B', for H(Budget%),I(Second_Scan%) and J(Retailer%) we create column 'C'.

zhaohaifeng_0-1594862401248.png

Below screenshow was what we want to achive,

zhaohaifeng_1-1594862676129.png

 

Is it possible for this setting in visualization level? Thanks.

Best Regards,

Haifeng

 

Labels (1)
5 Replies
Highlighted
Contributor III
Contributor III

Hi All,

 

Current we tried to create the new field named 'Filter' with values A/B/C, now we create Column Filter with field Filter, then in the measure we updated the expression with below sample,

pick(Match(Filter,'A'),
Sum({<Type={"CurrentYear_rolling"}>}[Sales QTY]),
Match(Filter,'B'),Sum({<Type={"preYear_rolling"},family={"update"}>}[Sales QTY]),
Match(Filter,'C'),Sum({<Type={"preYear_rolling"},family={"dete"}>}[Sales QTY]),
)

then for other expression will be null value, how can we hide null value in this? Or the way not possible? Thanks .

Highlighted
Creator III
Creator III

Let check below settings:

1st: Column -> Limitation

2nd: Uncheck Include zero values in Data Handling.

Hope it help.  

col_limitation.png  

DataHandling.png

Additionally, I think we can quickly archive this, by data model like this:

pivot.png

data_load.png

Contributor III
Contributor III

Hi @thi_pham , Thanks for your help.

 

But as checked, it not works, i m not sure whether somewrong in my end, attached the sample qvf here, can you help to check and share the ideas. Thanks very much.

 

Best Regards,

Haifeng

Highlighted
Contributor III
Contributor III

Hi all,

 

Also shared the sample qvf here, please help to share some ideas. Thanks in advance.

Highlighted
Creator III
Creator III

Sorry, I tried but still couldn't make it work.

The only solution might be make the pivot and pre-calculated value for the measures in dataload like what I did in above illustration. I'll not the perfect solution, because fitlters on GUI will not be updated but reload.

No idea why pivot table don't hide the null columns, hmmm