topic Re: Count of Text In Pivot Table Column in App Development

Count of Text In Pivot Table Column

Thu, 12 Jan 2017 18:38:17 GMT

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!

Re: Count of Text In Pivot Table Column

sunny_talwar — Thu, 12 Jan 2017 18:52:20 GMT

May be like this:

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

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

Re: Count of Text In Pivot Table Column

Thu, 12 Jan 2017 19:00:14 GMT

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?

Re: Count of Text In Pivot Table Column

sunny_talwar — Thu, 12 Jan 2017 19:20:16 GMT

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

Re: Count of Text In Pivot Table Column

Thu, 12 Jan 2017 19:32:52 GMT

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

Re: Count of Text In Pivot Table Column

sunny_talwar — Thu, 12 Jan 2017 19:40:20 GMT

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?

Re: Count of Text In Pivot Table Column

sunny_talwar — Thu, 12 Jan 2017 19:41:22 GMT

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.

Re: Count of Text In Pivot Table Column

Thu, 12 Jan 2017 19:43:45 GMT

Just checked that and yes it also returns 11

Re: Count of Text In Pivot Table Column

sunny_talwar — Thu, 12 Jan 2017 19:49:33 GMT

Can you share a screenshot of Listing Status field?

Re: Count of Text In Pivot Table Column

Thu, 12 Jan 2017 19:56:41 GMT

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

Re: Count of Text In Pivot Table Column

sunny_talwar — Thu, 12 Jan 2017 19:59:00 GMT

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

Re: Count of Text In Pivot Table Column

Thu, 12 Jan 2017 20:02:10 GMT

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

Re: Count of Text In Pivot Table Column

sunny_talwar — Thu, 12 Jan 2017 21:50:00 GMT

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

Re: Count of Text In Pivot Table Column

Thu, 12 Jan 2017 22:00:17 GMT

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

Re: Count of Text In Pivot Table Column

sunny_talwar — Thu, 12 Jan 2017 23:12:03 GMT

How about this?

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

Re: Count of Text In Pivot Table Column

Fri, 13 Jan 2017 15:06:07 GMT

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!

Re: Count of Text In Pivot Table Column

sunny_talwar — Fri, 13 Jan 2017 15:07:36 GMT

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

Re: Count of Text In Pivot Table Column

Digvijay_Singh — Fri, 13 Jan 2017 16:15:37 GMT

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