Qliksense pivot table Create column based on measures

[zhaohaifeng]

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'.

Below screenshow was what we want to achive,

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

Best Regards,

Haifeng

[zhaohaifeng]

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 .

[thi_pham]

Let check below settings:

1st: Column -> Limitation

2nd: Uncheck Include zero values in Data Handling.

Hope it help.  

  

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

[zhaohaifeng]

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

[zhaohaifeng]

Hi all,

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

[thi_pham]

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