Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help needed on expression

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.

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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

Capture.PNG.png

View solution in original post

7 Replies
JonnyPoole
Employee
Employee

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)

Not applicable
Author

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

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

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.

Not applicable
Author

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

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

Capture.PNG.png

Not applicable
Author

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.