topic Re: Pivot table Layout help??? in QlikView

Pivot table Layout help???

amit_saini — Wed, 10 Aug 2016 10:32:49 GMT

Hi Folks,

Below is my Pivot table :

Result when no specific "Plant" is selected from list

Now if I select any Plant from list say "KBC" , I'm having below results:

Here above "KBC" shifted right after Dimension after selection from Plant list

Desired Layout should be like below : The position of columns should not change based on any selection

Please help hoe this can be done!

Thanks,
AS

Re: Pivot table Layout help???

Anonymous — Wed, 10 Aug 2016 10:35:40 GMT

use set Analysis something like

sum({<Plant_Short_...=>}yourvalue)

you need to insert your fullname of the Dimension "Plant_Short_)

means, Show value regardless of selection of Plant_short_..

Re: Pivot table Layout help???

sunny_talwar — Wed, 10 Aug 2016 10:36:29 GMT

The other plants are still available they just move to the back of selected plant? Are you using a sorting expression? May be you have to ignore selection in your sorting expression using {1}

Re: Pivot table Layout help???

amit_saini — Wed, 10 Aug 2016 10:42:22 GMT

Hi,

Sum({<Plant_Short_Name=>}Value)

I have already tried this option , but no success.

Thanks,

Re: Pivot table Layout help???

Anonymous — Wed, 10 Aug 2016 10:42:41 GMT

If you want your PVIOT TABLE not changed irrespective any selections .

Please try this in the Properties ...>General...>(select)Detached

Re: Pivot table Layout help???

amit_saini — Wed, 10 Aug 2016 10:44:41 GMT

Hi Sunny,

Only selected Plant is available on selection , can't see other Plants.

Yes I'm doing below sorting on Plant orders based on Customer requirement:

Dual(Plant_Short_Name,Match(Plant_Short_Name,'KB2','KBC','KBK','KBL','KIS','KBO','KBR','KDU','KHE','KIS','KKS','KOF','KRO','KUK','KUL','KWK','KDT','CWC','KLA','KMX','KOC',

'KSI','KTX','KUS','KCC','KCQ','KSH','KGZ','KJP','KZC'))

Thanks,

Re: Pivot table Layout help???

amit_saini — Wed, 10 Aug 2016 10:45:47 GMT

Hi,

I want selection to be performed in table , by doing Detached they will not work.

Thanks,

Re: Pivot table Layout help???

sunny_talwar — Wed, 10 Aug 2016 10:47:16 GMT

Can you try this:

Match(Only({1}Plant_Short_Name),'KB2','KBC','KBK','KBL','KIS','KBO','KBR','KDU','KHE','KIS','KKS','KOF','KRO','KUK','KUL','KWK','KDT','CWC','KLA','KMX','KOC','KSI','KTX','KUS','KCC','KCQ','KSH','KGZ','KJP','KZC'))

Re: Pivot table Layout help???

sunny_talwar — Wed, 10 Aug 2016 10:49:31 GMT

And may be with this expression (just for testing)

Sum({1}Value)

Re: Pivot table Layout help???

amit_saini — Wed, 10 Aug 2016 10:57:33 GMT

Sorry not working

Thanks,

Re: Pivot table Layout help???

sunny_talwar — Wed, 10 Aug 2016 10:59:27 GMT

Do you mind putting this things here:

1) Dimension (list all dimensions whether field or calculated dimensions)

2) Expression

3) Sorting Expression

Re: Pivot table Layout help???

amit_saini — Wed, 10 Aug 2016 11:04:34 GMT

Hi,

1) Dimension (list all dimensions whether field or calculated dimensions):

Plant_Short_Name

2) Expression

Expression1:

=Sum({$ <

Period_Type={'R12'}

,Year_EHS=, Quarter=,Month_EHS=, Year_Qtr=, Year_EHS=, Date_EHS=

,AsOf_Year_Month={"$(=Date(Max({$} AsOf_Year_Month),'YYYY-MM'))"}

// ,AsOf_DateValue={">=$(=MonthStart(AddMonths(Max({$} AsOf_DateValue),-11)))<=$(=Max({$} AsOf_DateValue))"}

// ,active_site={1,2}

,%row_type_code={4}

,$(vDailyLogDims)

,$(vIssueTrackingDimensions)

TRI * $(vInjRateFactor))

Sum({$ <

Period_Type={'R12'}

,Year_EHS=, Quarter=,Month_EHS=, Year_Qtr=, Year_EHS=, Date_EHS=

,AsOf_Year_Month={"$(=Date(Max({$} AsOf_Year_Month),'YYYY-MM'))"}

// ,AsOf_DateValue={">=$(=MonthStart(AddMonths(Max({$} AsOf_DateValue),-11)))<=$(=Max({$} AsOf_DateValue))"}

// ,active_site={1,2}

,%row_type_code={4}

,$(vDailyLogDims)

,$(vIssueTrackingDimensions)

Manhours)

Expression2:

if(sum([Act Planned Production Hours]) = 0, avg([Act OEE]),

sum([Act OEE]*[Act Planned Production Hours])/sum([Act Planned Production Hours]))

Expression 3:

=If( if(Year=2013,avg([Act. Tanks Scrapped as % of Tanks Produced]),(Sum([Act. Tanks Scrapped (Total Line)])/sum([Act BMM Shells Produced])))<>0,

if(Year=2013,avg([Act. Tanks Scrapped as % of Tanks Produced]),(Sum([Act. Tanks Scrapped (Total Line)])/sum([Act BMM Shells Produced]))),

null()

)

3) Sorting Expression

Dual(Plant_Short_Name,Match(Plant_Short_Name,'KB2','KBC','KBK','KBL','KIS','KBO','KBR','KDU','KHE','KIS','KKS','KOF','KRO','KUK','KUL','KWK','KDT','CWC','KLA','KMX','KOC',

'KSI','KTX','KUS','KCC','KCQ','KSH','KGZ','KJP','KZC'))

Thanks,

Re: Pivot table Layout help???

sunny_talwar — Wed, 10 Aug 2016 11:15:03 GMT

Try out these changes:

Expression1:

=Sum({$ <

Period_Type={'R12'}

,Year_EHS=, Quarter=,Month_EHS=, Year_Qtr=, Year_EHS=, Date_EHS=, Plant_Short_Name=

,AsOf_Year_Month={"$(=Date(Max({$<Plant_Short_Name=>} AsOf_Year_Month),'YYYY-MM'))"}

// ,AsOf_DateValue={">=$(=MonthStart(AddMonths(Max({$<Plant_Short_Name=>} AsOf_DateValue),-11)))<=$(=Max({$<Plant_Short_Name=>} AsOf_DateValue))"}

// ,active_site={1,2}

,%row_type_code={4}

,$(vDailyLogDims) -> Not sure what this variable contains

,$(vIssueTrackingDimensions) -> Not sure what this variable contains

TRI * $(vInjRateFactor)) -> Not sure what this variable contains

Sum({$ <

Period_Type={'R12'}

,Year_EHS=, Quarter=,Month_EHS=, Year_Qtr=, Year_EHS=, Date_EHS=, Plant_Short_Name=

,AsOf_Year_Month={"$(=Date(Max({$<Plant_Short_Name=>} AsOf_Year_Month),'YYYY-MM'))"}

// ,AsOf_DateValue={">=$(=MonthStart(AddMonths(Max({$<Plant_Short_Name=>} AsOf_DateValue),-11)))<=$(=Max({$<Plant_Short_Name=>} AsOf_DateValue))"}

// ,active_site={1,2}

,%row_type_code={4}

,$(vDailyLogDims)-> Not sure what this variable contains

,$(vIssueTrackingDimensions)-> Not sure what this variable contains

Manhours)

Expression2:

if(sum({$<Plant_Short_Name=>}[Act Planned Production Hours]) = 0, avg({$<Plant_Short_Name=>}[Act OEE]),

sum({$<Plant_Short_Name=>}[Act OEE]*[Act Planned Production Hours])/sum([Act Planned Production Hours]))

Expression 3:

=If( if(Only({$<Plant_Short_Name=>}Year)=2013,avg({$<Plant_Short_Name=>}[Act. Tanks Scrapped as % of Tanks Produced]),(Sum({$<Plant_Short_Name=>}[Act. Tanks Scrapped (Total Line)])/sum({$<Plant_Short_Name=>}[Act BMM Shells Produced])))<>0,

if(Only({$<Plant_Short_Name=>}Year)=2013,avg([Act. Tanks Scrapped as % of Tanks Produced]),(Sum({$<Plant_Short_Name=>}[Act. Tanks Scrapped (Total Line)])/sum({$<Plant_Short_Name=>}[Act BMM Shells Produced]))),

null()

)

3) Sorting Expression

Dual(Plant_Short_Name,Match(Only({1}Plant_Short_Name),'KB2','KBC','KBK','KBL','KIS','KBO','KBR','KDU','KHE','KIS','KKS','KOF','KRO','KUK','KUL','KWK','KDT','CWC','KLA','KMX','KOC','KSI','KTX','KUS','KCC','KCQ','KSH','KGZ','KJP','KZC'))

Re: Pivot table Layout help???

amit_saini — Wed, 10 Aug 2016 11:23:28 GMT

Thank u Sir