topic Create static column in Pivot table in QlikView

Create static column in Pivot table

mugdhaojha — Wed, 20 Sep 2017 12:45:58 GMT

Hi ,

I would like to create a table like the below screenshot where Columns in grey are dimensions in a database table (Month-year is horizontally aligned), Columns in yellow are static and column in orange is the label of the expression e.g Samples taken = Sum(sample_taken).

I have currently saved the static part of the table in an excel sheet and loading it in the application and Summary column as expression but the expression is getting repeated at each KPI Level 1.

How can I achieve this using a pivot table?

Thanks in advance,

Mugdha.

Re: Create static column in Pivot table

pradosh_thakur — Wed, 20 Sep 2017 13:55:34 GMT

if i understood correctly by static you mean Qlikview should not be allowed to select from those three column.

i can think of a few solutions here.

1: create a text box with transparent color and put it above those column.

2: add those three expression in set expression like sum({ < static column = {*}>}sample_taken)

PS: The second option might create problem if the static column are part of some hierarchy.

best of luck!

regards

Pradosh

Re: Create static column in Pivot table

mugdhaojha — Wed, 20 Sep 2017 15:09:59 GMT

Thank you for your reply!

Apologies, I wasnt clear. By static column I mean those columns/ rows will be repeated for each LA (second column) in the Region.

KPI level2,Objective and Summary columns is in a hieracrchy.

I am not sure how I can achieve this in a pivot table since when adding Summary column as expression each of the expression is getting repeated for KPI level 2 and Objective but I would like to have Samples taken and Samples required expression under 12.a (KPI Level 2 column) and Sampling (Objective column) only for LA - LA1 and should not be repeated for other values of KPI level 2 and Objective.

Hope this makes sense.

Re: Create static column in Pivot table

pradosh_thakur — Thu, 21 Sep 2017 10:07:01 GMT

do you have a sample qvw which you can post ?