Count of Text In Pivot Table Column

[#1]

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!

[sunny_talwar]

May be like this:

If(Dimensionality()=1,Count(DISTINCT {<FieldName1 = {'SWAP'}>} FieldName2))

Where FieldName2 is a field which is contains, BRAND 1, BRAND 2, .....

[#3]

Hi Sunny,

Thanks for the quick reply

I tried this and I think it partially worked...

if(dimensionality()=1,Count(DISTINCT {<"Listing Status" = {'Swap To Powerplay'}>} Brand)

This account has 11 total brands listed, so the above returns 11 when it should only return the 7

Does FieldName work still is it is a custom label?

[sunny_talwar]

FieldName has to be a field from the script. Also, does the Listing Status contain Swap or Swap to Powerplay?

[#5]

It is Swap to Powerplay, i just put it as SWAP in the example.

I used the field name from Script and that is what returns 11 (which is a count of total Brands at the account).

[sunny_talwar]

Well your expression should be counting only those brands where Listing Status = Swaps to Powerplay at least once. Do you see anything wrong with my interpretation above?

[sunny_talwar]

Also, what do you get for this?

if(dimensionality()=1,Count(DISTINCT Brand))

Still gives 11?

Make sure Listing Name is the exact name of your dimension. Even the upper case and lower case should match.

[#8]

Just checked that and yes it also returns 11

[sunny_talwar]

Can you share a screenshot of Listing Status field?

[#10]

Although the below screen shot only shows 7 brands, there are 4 more that are excluded from the list for another criteria.  The total brand at this account is 11 which is what this expression returns;

if(dimensionality()=1,Count(DISTINCT Brand))