topic Re: Pivot Table Column Issue in QlikView

Pivot Table Column Issue

waqasshah — Mon, 22 Oct 2018 15:57:52 GMT

Hi Guys,

I need you help. Actually I am trying to hide the column during calculation if there is zero or Null value. Please review the attached qvw file.

First thing is I have calculated the total number of jobs done in these financial years and then the difference between the jobs in these financial years.

As you have noticed in Difference tab financial year 2016/2017 is zero because we do not have data for previous financial years like 2014/2015 or 2015/2016. I basically want to hide the column "2016/2017" because it is showing zero values. can someone please help ?

Re: Pivot Table Column Issue

sunny_talwar — Tue, 23 Oct 2018 11:22:18 GMT

Like this (bottom table)?

Check attached

Re: Pivot Table Column Issue

waqasshah — Tue, 23 Oct 2018 13:10:16 GMT

Thanks Sunny thats what I want but what is the logic you applied ?

(To be honest I was thinking about you.. and you came and solve the problem )

Re: Pivot Table Column Issue

sunny_talwar — Tue, 23 Oct 2018 13:13:29 GMT

I have attached the app for you to look at... but here are the changes made

Script - Added a new Island table

Table:

LOAD * INLINE [

DepartName, FinancialYear, JobsDone

ABC, 2016/2017, 1500

ABC, 2016/2017, 1200

ABC, 2016/2017, 900

DEF, 2016/2017, 300

DEF, 2016/2017, 400

GHI, 2016/2017, 400

GHI, 2016/2017, 200

ABC, 2017/2018, 900

ABC, 2017/2018, 100

ABC, 2017/2018, 1600

ABC, 2017/2018, 900

DEF, 2017/2018, 325

DEF, 2017/2018, 100

GHI, 2017/2018, 900

GHI, 2017/2018, 50

ABC, 2018/2019, 1100

ABC, 2018/2019, 1200

ABC, 2018/2019, 60

DEF, 2018/2019, 10

DEF, 2018/2019, 25

GHI, 2018/2019, 150

GHI, 2018/2019, 350

];

Dim:

LOAD * INLINE [

Dim

];

Chart - 3 Dimensions and 1 Expression (instead of 2 dimension and 2 expressions)

Dimensions

DepartName

=Pick(Dim, 'Total Jobs Done', 'Difference')

FinancialYear

Expression

Pick(Dim, Sum(JobsDone), Sum(JobsDone)-Before(Sum(JobsDone)))

Re: Pivot Table Column Issue

waqasshah — Tue, 23 Oct 2018 13:22:15 GMT

Thanks One more thing just for example I ant another expression to calculate . jyst say a percentage then I have to add ISLAND table like this

Dim:

LOAD * INLINE [

Dim

];

For percentage I have to add another expression then ?

Re: Pivot Table Column Issue

sunny_talwar — Tue, 23 Oct 2018 13:27:26 GMT

Yes, sir

Re: Pivot Table Column Issue

waqasshah — Tue, 23 Oct 2018 13:35:29 GMT

Many Many Thanks Sunny. You are really a star..... Thanks again

Re: Pivot Table Column Issue

sunny_talwar — Tue, 23 Oct 2018 13:49:37 GMT

No problem at all

Re: Pivot Table Column Issue

waqasshah — Tue, 23 Oct 2018 14:56:55 GMT

Sunny I am trying to understand how Pick function exclude the financial year 2016/2017? we did not mention anything.. .. or did you put any other expression to hide? apologise for silly question but Thanks for your help again

Re: Pivot Table Column Issue

sunny_talwar — Tue, 23 Oct 2018 15:05:10 GMT

0's excluded because we have checked 'Suppress zero values' on the presentation tab

Re: Pivot Table Column Issue

waqasshah — Tue, 23 Oct 2018 15:31:19 GMT

Thanks again. it means any value if it is NULL or zero in that complete column the column will be hide..

Apologise for asking so many question. I have changed the qlikview file by adding another column its fine.

When I am trying to implement the logic on live the column displayed with financial year (2016/2017) heading ....

Re: Pivot Table Column Issue

waqasshah — Tue, 23 Oct 2018 15:32:23 GMT

So far the conclusion is first we need to add ISLAND table and identify number of columns we want to display and then specify in dimension the column names and then in expression we need to use the same number column of columns and always make sure Suppress zero value option is checked.. correct me if I am wrong

Re: Pivot Table Column Issue

waqasshah — Fri, 26 Oct 2018 09:32:16 GMT

Sunny I got the answer. Actually its a versioning issue. Your solution works on latest version but no on old version. my qlikview version is Version 11.20 SR 13.

Thanks again for your help. Really appreciated.

Kind Regards

Waqas Shah

Re: Pivot Table Column Issue

sunny_talwar — Fri, 26 Oct 2018 11:07:03 GMT

I think it might still work on a different SR of version 11.20. Have you tried to upgrade or downgrade from SR13? Also, please close the thread if you have got what you wanted.

Best,

Sunny

Re: Pivot Table Column Issue

waqasshah — Fri, 26 Oct 2018 11:29:41 GMT

I will try upgrade.. How to close the thread ?

Re: Pivot Table Column Issue

sunny_talwar — Fri, 26 Oct 2018 11:32:47 GMT

Check here

Qlik Community Tip: Marking Replies as Correct or Helpful