Text considered as numeric

[Anonymous]

Hi All

In my pivot table I have applied below condition to display or hide. I have kept list box for QUARTER, so if I select any QUARTE(s) than display otherwise not.

When I open QV file it displays all QUARTERs by default and which is fine.

=SubStringCount(Concat(QUARTER, '|'), 'Q1')

Top of above in my expressions I have QUARTERs 'Q1' to 'Q13', 'Q17' and 'Q21'. now in my data I only have Q13, Q17 and Q21 QUARTERs  values only but it also pop up QUARTERs for Q1, Q2 with zeroes value and heading name (=MaxString({$<DataFrom ={'P'}, QUARTER = {'Q1'}>} ProjQtr) )  is null.

I noticed even after TEXT values(Q1, Q2, Q3..Q13, Q17, Q21) it's considered first position values (1 or 2) right after Q. and that's why Q1 and Q2 column values (in case of 'Q13', 'Q17', 'Q21') pop up.

Question is how can we apply condition specifically 'Q1', 'Q2' .... 'Q21' so it's look only specific QUARTERs not the first characted after Q

I am sorry to unable attach QV file as huge. Sample QV works fine

Thanks in advance

[Anonymous]

Long story in short

Let say if I have 'Q21' data only in my whole database. So it should pop up only Q21 column not all

But in addition it's also pop up Q2 column with header null and values zeroes

Thanks

[sunny_talwar · Accepted solution]

Not sure if I understand completely, but try this:

=SubStringCount(Concat(QUARTER, '|'), 'Q2|') with the pipe symbol (|) so that Q2 doesn't end up including Q21.

Best,

Sunny

[Anonymous]

Hi SUIndia

It's works fine, with pipe "|" it's ending to look furhter.

Thanks for your help

[sunny_talwar]

No problem