Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count of Text In Pivot Table Column

Hello!

In a pivot table, like the below, I want Dimension 1 (highest level of Pivot Table) to be a total of a text string in dimension 2 below

Example

PIZZA HUT, (Count of "Swap")

     BRAND 1, 'MAINTAIN'

     BRAND 2, 'SWAP'

     BRAND 3, 'SWAP'

     BRAND 4, 'SWAP'

     BRAND 5, 'SWAP'

     BRAND 6, 'SWAP'

     BRAND 7, 'SWAP'

     BRAND 8, 'SWAP'

JEREMYS BAR, (Count of "Swap")

     BRAND 1, 'MAINTAIN'

     BRAND 2, 'SWAP'

     BRAND 3, 'SWAP'

     BRAND 4, 'MAINTAIN'

     BRAND 5, 'SWAP'

     BRAND 6, 'MAINTAIN'

     BRAND 7, 'SWAP'

     BRAND 8, 'SWAP'

So for Pizza Hut, the total count should be 7.  For Jeremy's Bar, it would be 5.

I have tried;

If(Dimensionality()=1,Count('SWAP'))  This returns a value significantly larger than 7 (41 in my case).

If(Dimensionality()=1,Count(Distinct('SWAP'))  This returns 1

Any ideas would be greatly appreciated!

17 Replies
sunny_talwar

Listing Status looks more like an expression then a field from the script?

Not applicable
Author

Yes sorry it is.  It is a long if statement.  Anything at dimension level 3 will be evaluated on 3 criteria and return 1 of 3 responses.

my eventual goal is;

Dimensional Level 1 (615 ABN SR Southwest Edmonton) to be a Sum of Dimension level 2 (Canadian Brewhouse).

And Dimension 2 (Canadian Brewhouse) to be a count of "Swap to Powerplay" below

sunny_talwar

Can you share the expression you are using for Listing Status?

Not applicable
Author

Right now i have it so that Level 1 and 2 of Dimensionality return a placeholder 'Open To Review'.  Thats what i am trying to solve with this.

if(dimensionality()=1,'Open To Review',

if(dimensionality()=2,'Open To Review',

if(Dimensionality()=3,

if("R3M Volume"/3=0,"",

if(("R3M Volume"/3)<1,'Swap To Powerplay',

if(("R3M Volume"/3)<2,'Potential Swap',

if(("R3M Volume"/3)>2,'Maintain')))))))

sunny_talwar

How about this?

If(Dimensionality()=1, Sum(If("R3M Volume"/3 < 1, 1, 0)))

Not applicable
Author

I don't think that would work in this case as it would only work of the sum for Dimension 1 is lower than 1, as opposed to a count of all the Dimension level 3 that are meeting that criteria.  I appreciate your help though!

sunny_talwar

I might be able to help better if I have a sample to look at, but without one, I just understand the problem in bits and pieces

Digvijay_Singh

Not sure but you may need two measures to achieve your output, see if something like this can help, I used expressions here as suggested by stalwar1

Capture.PNG