Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
pinkzfairy
Contributor II
Contributor II

Pivot Table Partial Sum

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

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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

View solution in original post

5 Replies
Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
PrashantSangle

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

Can you explain little bit more???

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
balabhaskarqlik

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

antoniotiman
Master III
Master III

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

pinkzfairy
Contributor II
Contributor II
Author

Thanks a lot Antonio. It works