topic Re: Pivot Table Partial Sum in QlikView

Pivot Table Partial Sum

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

Hi All,

I have pivot table with Expression :

% Visibility = sum([Target Visibility Acv])/Count([Customer Key])

% Target = avg([Target Outlet Pct])

Visibility Acv = if(isnull([% Visibility]), Null(), if([% Visibility]>=[% Target],1,0))

Please help so i can get Total Visibility Acv like box in blue (from sum of visibility acv).

I also attach the QVW file.

Really really appreciate if anyone could help me on this.

Thanks a lot

Re: Pivot Table Partial Sum

Anil_Babu_Samineni — Tue, 04 Jul 2017 06:32:54 GMT

Some what i understand your intention but not fully. Here, One option may be Custom Format cell for that?

Re: Pivot Table Partial Sum

PrashantSangle — Tue, 04 Jul 2017 06:49:57 GMT

What was the logic behind the last column?????

Can you explain little bit more???

Regards,

Re: Pivot Table Partial Sum

BalaBhaskar_Qlik — Tue, 04 Jul 2017 06:54:18 GMT

If i understand correctly, there might be some issue with your Total Visibility Acv Expression, because of this issue it's not counting & sum all Visibility Acv values from Different Targets, Hence you're seeing wrong results in Total Visibility Acv.

(Your question is bit confusing: Currently your Total Visibility Acv values are like: 0s, 1s. You are expecting the results like the Yellow colored one, is it correct?)

Re: Pivot Table Partial Sum

antoniotiman — Tue, 04 Jul 2017 06:55:31 GMT

Hi,

try this

If(SecondaryDimensionality() > 0,
if(isnull(sum([Target Visibility Acv])/Count([Customer Key])), Null(),
if(sum([Target Visibility Acv])/Count([Customer Key])>=[Target Outlet Pct],1,0)
),
RangeSum(First(
if(isnull(sum([Target Visibility Acv])/Count([Customer Key])), Null(),
if(sum([Target Visibility Acv])/Count([Customer Key])>=[Target Outlet Pct],1,0)),1,NoOfColumns())))

Regards,

Antonio

Re: Pivot Table Partial Sum

pinkzfairy — Tue, 04 Jul 2017 07:43:07 GMT

Thanks a lot Antonio. It works