Pivot Table Column Issue

[waqasshah]

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 ?

[sunny_talwar]

Like this (bottom table)?

Check attached

[waqasshah]

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 )

[sunny_talwar]

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, 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, 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

   1

   2

];

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)))

[waqasshah]

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

   1

   2

    3

];

For percentage I have to add another expression then ?

[sunny_talwar]

Yes, sir

[waqasshah]

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

[sunny_talwar]

No problem at all

[waqasshah]

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

[sunny_talwar]

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