topic Re: customize Pivot table in QlikView

customize Pivot table

vengadeshpalani — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

i have pivot table like below but i need to show extra one row with blue color based on Dim-3 how can i achieve this requirement?

Actual

what we need

please give some idea...

Re: customize Pivot table

sunny_talwar — Wed, 21 Mar 2018 17:43:45 GMT

Check attached

Re: customize Pivot table

vengadeshpalani — Wed, 21 Mar 2018 18:21:59 GMT

thanks, Sunny

i have face the following issue

Re: customize Pivot table

sunny_talwar — Wed, 21 Mar 2018 18:23:35 GMT

Uncheck 'Suppress Zero Value' on the presentation tab and see if that helps

Re: customize Pivot table

vengadeshpalani — Wed, 21 Mar 2018 18:24:05 GMT

i made changes in following things

Dim3

=Pick(ExtraDim, if([FINANCIAL PLAN]<>'ZZZZZ',[FINANCIAL PLAN]), ' ')

Dim3 color

=If(ExtraDim = 2, LightBlue())

expression

Pick(ExtraDim, (if ([FINANCIAL PLAN] <>'ZZZZZ', SUM(AMOUNT),0)), 0)

expression color

=If(ExtraDim = 2, LightBlue())

sort

Only({1} ExtraDim)

Re: customize Pivot table

sunny_talwar — Wed, 21 Mar 2018 18:25:29 GMT

Uncheck 'Suppress Zero Value' on the presentation tab and see if that helps

Re: customize Pivot table

vengadeshpalani — Wed, 21 Mar 2018 18:39:11 GMT

Sunny,

i have Unchecked Suppress Zero Value, it does not work.

FYI --> by default following selection will happen, when i clear all it's working fine.

Re: customize Pivot table

vengadeshpalani — Wed, 21 Mar 2018 18:47:22 GMT

Sunny

Even i clear all selection but i facing the same issue (Suppress Zero Value Unchecked)

Re: customize Pivot table

sunny_talwar — Wed, 21 Mar 2018 18:48:12 GMT

May be try this

Dim3

=Pick(Only({1} ExtraDim), if([FINANCIAL PLAN]<>'ZZZZZ',[FINANCIAL PLAN]), ' ')

Dim3 color

=If(Only({1} ExtraDim) = 2, LightBlue())

expression

Pick(Only({1} ExtraDim), (if ([FINANCIAL PLAN] <>'ZZZZZ', SUM(AMOUNT),0)), 0)

expression color

=If(Only({1} ExtraDim) = 2, LightBlue())

Re: customize Pivot table

vengadeshpalani — Wed, 21 Mar 2018 19:03:23 GMT

Error In Calculated Dimension

Re: customize Pivot table

sunny_talwar — Wed, 21 Mar 2018 19:04:44 GMT

Of course... I am not thinking straight.. try this

Dim3

=Pick(ExtraDim, if([FINANCIAL PLAN]<>'ZZZZZ',[FINANCIAL PLAN]), ' ')

Dim3 color

=If(Only({1} ExtraDim) = 2, LightBlue())

expression

Pick(ExtraDim, (if ([FINANCIAL PLAN] <>'ZZZZZ', SUM(AMOUNT),0)), 0)

expression color

=If(Only({1} ExtraDim) = 2, LightBlue())

Re: customize Pivot table

vengadeshpalani — Wed, 21 Mar 2018 19:23:24 GMT

Re: customize Pivot table

vengadeshpalani — Wed, 21 Mar 2018 19:24:45 GMT

it's not working Sunny.values are showing as null

Re: customize Pivot table

sunny_talwar — Wed, 21 Mar 2018 19:34:21 GMT

Can you share what you have?

Re: customize Pivot table

vengadeshpalani — Thu, 22 Mar 2018 11:04:30 GMT

the issue is if anyone of month column is null means it applies color

Root cause --> if the total value is zero or null means we want to apply color.

is it possible in Pivot Table?

Re: customize Pivot table

vengadeshpalani — Thu, 22 Mar 2018 11:45:10 GMT

How to apply total value in all month Dim value?

Re: customize Pivot table

jonathandienst — Thu, 22 Mar 2018 12:23:56 GMT

The problem is that the cell is probably not null, but missing. Missing values are never calculated, so the colour expression cannot fix the problem. The solution could be to backfill the missing values with zeroes or nulls.

NULL handling in QlikView

The Importance of Nothing