Help needed on expression

[#1]

Hi All,

Need 2 expressions as below.

1.

I have to store values in my variable.

vStatus = ('Status', New Status', 'Old Status', 'Future Status')

2. then I have to get total count of the field where above values are in.

=TextCount(IF(Field = vStatus))

Can anyone give suggestion on this?

Thanks.

[JonnyPoole]

vStatus:     concat( distinct [Status] , ',')     -> will give you a comma delimitted list of all the distinct values in the field [Status]

substringcount(  $(vStatus) ,  ',')  + 1   -> will count the number of commas in the first string (and add 1)

[#3]

Hi Jonathan,

My case may not be all the time word has "Status".

vStatus = ('Deleted', 'Ignored','Status', New Status', 'Old Status', 'Future Status')

I am not sure wheather it is possible or not, I want to keep all those words in variable and want to compare. Because those words may be increase/decrease so I just change variable values infuture instead of changing expression in the dashboard.

1. Needs to keep all the words/values here.

2. compare with variable. If any word existed then count as 1.

Thanks.

[JonnyPoole]

That is OK, when I wrote [Status] i meant  'Status' as a field name , not a field value.  If your field was called 'Call Status'

If you want the list to never change and just have all possible values just add a {1} to the concat() expression:

vStatus:     concat(  {1}  distinct [Status] , ',')     -> this evaluates the formula in the global context regardless of user selections, it will always have every possible Status no matter what the user selects.

Is that what you want ?

[JonnyPoole]

for # 2 , you would just use this to see if the string contained in the variable 'vOtherVariable' is in the list of delimitted values in 'vStatus':

substringcount( $(vStatus) ,   $(vOtherVariable) )

Only concern is if the vOtherVariable contains a word that could exist in multiple places in vStatus... like the value 'South' existing in both of the following:     'South','South America'  ... but you only want it to hit on 'South' and not 'South America'. 

To account for that an additional twist is needed but let me know if that is needed for your use case.

[#6]

Hi Jonathan,

I understand your solution. But my case is different, Kindly refer to the attached QVW.

If in future under field (F3), If I get more values (e.g. Status 4, Status 5 etc...), how can I show?

Thanks.

[JonnyPoole · Accepted solution]

Wow. This is completely different requirement. Thank you for posting your QVW / screenshot.

I altered your technique to make all test and status dynamic so the whole chart is data driven with no manual intervention needed when new status arrive.

My only question is that you had 'values on data point' before and it was only partially working. What is the requirement there ( if this is good) ?

[#8]

Hi Jonathan,

The % I needs to show for only some of the Status only.

Thanks for your help. Let me implement into actual requirement and let you know.

Once again thanks for your time.